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

Question

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 1

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:

• Sort by column ASC, but NULL values first?

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

• Select first row in each GROUP BY group?

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

• Optimize GROUP BY query to retrieve latest row per user
• Optimize groupwise maximum query

Answer 2

Try this one:

SELECT t1.* FROM Table1 t1 JOIN  (    SELECT category, MAX(date) AS MAXDATE    FROM Table1    GROUP BY category ) t2 ON T1.category = t2.category AND t1.date = t2.MAXDATE 

See this SQLFiddle