Select arbitrary row for each group in Postgres-CodePudding

In Presto, there's an arbitrary() aggregate function to select any arbitrary row in a given group. If there's no group by clause, then I can use distinct on. With group by, every selected column must be in the group by or be an aggregated column. E.g.:

| id | foo |
| 1  | 123 |
| 1  | 321 |

select id, arbitrary(foo), count(*)
from mytable
group by id

Fiddle

It doesn't matter if it returns 1, 123, 2 or 1, 321, 2. Something like min() or max() works, but it's a lot slower.

Does something like arbitrary() exist in Postgres?

CodePudding user response：

You can join with aggregated table, then you will be able to use distinct on:

select distinct on (id) id, t.foo, it.cnt
from mytable t
join (
  select id, count(*) cnt
  from mytable
  group by id) it using(id);

CodePudding user response：

select  m.foo,b.id,b.cnt from mytable m  
join (select id, count(*) cnt
from mytable
group by id) b  using (id)  limit 1;

If not explicit mention asc, desc all the order is not guaranteed. Therefore in the above query the foo's appearance is arbitrary.