Search code examples
pythonsqlpostgresqlmaxgreatest-n-per-group

Postgres select * after groupby and max

Consider a table with an id PK and 26 columns. There is an index on (a,b)

id | a | b | c | d | e ... | z |
--------------------------------

I'm trying to select the row where the unique pairing of (a,b) is most recent. IE what was the last record for each (a,b) Because ID is autoincrementing, know that the max is the last row.

SELECT MAX(id), a, b
FROM table GROUP BY (a, b)

However, is there any way to have the SELECT show all columns without manually listing them, eg.

SELECT MAX(ID), a, b, c ... z

I've tried the following to no avail

SELECT MAX(ID), *
SELECT MAX(ID), table.*
Solution

  • In Postgres, distinct on comes handy for this:

    select distinct on (a, b) t.*
from mytable t
order by a, b, id desc