Search code examples
sqlpostgresqlgreatest-n-per-group

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?

Solution

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

    SELECT DISTINCT ON (category)
       id  -- , category, date  -- any other column (expression) from the same row
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 simple and fast. Detailed explanation in this related answer:

    For big tables with many rows per category consider an alternative approach: