user2412043 user2412043 - 1 year ago 172
SQL Question

How to select id with max date group by category in PostgreSQL?

For an example, I would like to select id with max date group by category,
the result is: 7, 2, 6

id category date
1 a 2013-01-01
2 b 2013-01-03
3 c 2013-01-02
4 a 2013-01-02
5 b 2013-01-02
6 c 2013-01-03
7 a 2013-01-03
8 b 2013-01-01
9 c 2013-01-01


May I know how to do this in PostgreSQL?

Answer Source

This is a perfect use-case for DISTINCT ON (Postgres specific extension of standard DISTINCT):

SELECT DISTINCT ON (category)
       id
FROM   tbl
ORDER  BY category, "date" DESC;

Careful with descending sort order. If the column can be NULL, you may want to add NULLS LAST:

DISTINCT ON is simplest and fast. Detailed explanation in this related answer:

For big tables consider this alternative approach:

Performance optimization for many rows per category:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download