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

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

Try this one:

SELECT t1.* FROM Table1 t1JOIN (   SELECT category, MAX(date) AS MAXDATE   FROM Table1   GROUP BY category) t2ON T1.category = t2.categoryAND t1.date = t2.MAXDATE

See this SQLFiddle

Another approach is to use the first_value window function: http://sqlfiddle.com/#!12/7a145/14

SELECT DISTINCT  first_value("id") OVER (PARTITION BY "category" ORDER BY "date" DESC) FROM Table1ORDER BY 1;

... though I suspect hims056's suggestion will typically perform better where appropriate indexes are present.

A third solution is:

SELECT  idFROM (  SELECT    id,    row_number() OVER (PARTITION BY "category" ORDER BY "date" DESC) AS rownum  FROM Table1) xWHERE rownum = 1;