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