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

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

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

Tags:

Sql

Postgresql

Greatest N Per Group

Related