How to select id with max date group by category in PostgreSQL?
SqlPostgresqlGreatest N-per-GroupSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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:
Solution 2 - Sql
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
Solution 3 - Sql
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 Table1
ORDER BY 1;
... though I suspect hims056's suggestion will typically perform better where appropriate indexes are present.
A third solution is:
SELECT
id
FROM (
SELECT
id,
row_number() OVER (PARTITION BY "category" ORDER BY "date" DESC) AS rownum
FROM Table1
) x
WHERE rownum = 1;