sql for finding most recent record in a group

Question

i have a table like such

table_id | series_id | revision_id | year
------------------------------------------
1        | 1         | 1           | 2010
2        | 2         | 1           | 2009
3        | 2         | 2           | 2008
4        | 2         | 2           | 2009
5        | 2         | 3           | 2010
6        | 2         | 3           | 2008
7        | 3         | 2           | 2007
8        | 3         | 3           | 2010
9        | 3         | 3           | 2010

I need to find the table_id for the max(year) when grouped by revision_id when series = X in postgresql.

eg : when x =2 i expect this result

table_id | series_id | revision_id | year
------------------------------------------
2        | 2         | 1           | 2009
4        | 2         | 2           | 2009
5        | 2         | 3           | 2010

this doesn't work

SELECT * from table 
WHERE series_id = 2 
AND table_id IN (
    SELECT table_id 
    FROM table 
    WHERE series_id = 2 
    GROUP by revision 
    ORDER BY year DESC
)

I cannot figure out a way to do this in postgresql since I need to return a field i am not grouping by

here are some similar problems in other SQL flavors.

MySQL SQL Query, Selecting 5 most recent in each group

SQL SERVER SQL Server - How to select the most recent record per user?

mysql allows you to select a field that is not in the group by, which is not allowed in postgres. SQL server's TOP1 is not supported either. — David Chan, Jul 05 '11 at 22:50
Unless you are somehow picking only the top record, I do not reccommend ordering by anthing in a subquery, as the sort is discarded immediately afterwards (by SQL standard). — Clockwork-Muse, Jul 05 '11 at 23:08
possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Jun 12 '14 at 09:08

score 3 · Accepted Answer · answered Jul 05 '11 at 23:52

Query:

SELECT table_id, series_id, revision_id, year
FROM tableName t INNER JOIN
    (SELECT revision_id, max(year) AS year
    FROM tableName 
    WHERE series_id = 2 
    GROUP BY revision_id) s
USING (revision_id, year)
WHERE series_id = 2;

Result:

 table_id | series_id | revision_id | year
----------+-----------+-------------+------
        2 |         2 |           1 | 2009
        4 |         2 |           2 | 2009
        5 |         2 |           3 | 2010
(3 rows)

score 2 · Answer 2 · answered Jul 05 '11 at 22:55

2

Hmm... Try this:

SELECT *
FROM table as a
WHERE series_id = ?
AND year = (SELECT MAX(year)
            FROM table as b
            WHERE a.series_id = b.series_id
            AND a.revision_id = b.revision_id)

answered Jul 05 '11 at 22:55

Clockwork-Muse

12,806
6
31
45

sql for finding most recent record in a group

2 Answers2

Linked