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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT id FROM tbl GROUP BY cat HAVING MAX date

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

Examples related to sql

Examples related to postgresql

Examples related to greatest-n-per-group