Does trino support distinct on or have other way to do same work

[cntwelve]

Hi, everyone:
I have a large dataset, which has three columns to be used: two cloumn as key and the 3rd column that shows the action time. And these column has one-2-many relation.
I want to pick the key(two columns), and anyone of the 3rd column value. PG can use 'distinct on', it's not standard, but convenient. Does Trino support this? Or other way to resolve this task(because the dataset is huge, and one key have too many rows).

Thanks a lot!

[chenjian2664]

Could you share the example about the goal, i.e. what's the sql looks like ?

[wlfbck]

Not the original author, but running into a similar problem where distinct on would solve the issue more elegantly.

We have a measurement table which has columns time, device_id, node_id, value and i want to visualize these in grafana (where trino is our datasource). Now node_id is whatever sensoric hardware is attached to the device, so this is always the same for a given device_id, with long timeseries per node_id.
Some of these timeseries are very sparse, with a datapoint coming in like every ~12hours or even longer. Others can be in the millisecond range.

Now if i want to take a look at, say, an hour long timeperiod, and there happens to be no datapoint from the sparse data, it will not visualize these. The values are still valid and interesting though.

So i have to build some rather awkward and long query constructs with likely terrible performance, since distinct on is missing. If i had it, i could just use distinct on node_id (like shown in PG manual here) and be done with it. With hopefully better performance :)