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 |
--------------------------------

​x
 
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)

 
SELECT MAX(id), a, bFROM 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

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

I’ve tried the following to no avail

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

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

Answer

In Postgres, distinct on comes handy for this:

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

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

Advertisement

Answer