How to select * in addition to group by?

user_4076764 0
Viewed 55

Consider a PostgreSQL table with fields a-z

a, b, c ... z
-------------
5, 6, 2 ... 9
5, 6, 3 ... 1

I'd like to do a group on fields a,b and keep only records where b was maximum.

SELECT a, max(b) as b, c, d, e ... z
FROM table
GROUP BY a, b

This works fine, but it's annoying to have to type out all the values in SELECT. I'd much rather do something like

SELECT max(b) as b, * 
FROM TABLE

But doing so gives error

[42803] ERROR: column "table.id" must appear in the GROUP BY clause or be used in an aggregate function.

Any idea how to avoid having to type all the column names in a lengthy table when doing a groupby operation?

postgresql sql
2 Answers

You can use rank():

select t.*
from (select t.*, rank() over (partition by a order by b desc) as seqnum
      from t
     ) t
where seqnum = 1;

Actually, in Postgres, the fastest method is usually distinct on:

select t.*
from t
order by a, b desc;

With an index on (a, b desc) this should be the fastest method.

user_11440350

Gordon Linoff's answer put me on the right track, namely using distinct on. This works in postgres

SELECT DISTINCT ON (a, b) *
FROM table
ORDER BY a, b DESC

Basically it lists the distinct rows of (a,b) and sorts them in order, hence taking only the first or last value depending on sort order. Actually surprised this works...

user_40767640
Related