Select all records of type 'x' that are the highest 'version'

Question

Given table:

Agreement:
    id: int
    type: string
    version: int

Given Records like:

1 | Terms | 1
2 | Terms | 2
3 | Privacy | 1

I'd like a query that outputs:

2 | Terms | 2
3 | Privacy | 1

I've tried all manner of distinct and self joins to max(version), I just can't seem to crack it.

Answer 1

The easiest thing would probably be to use the rank window function:

SELECT id, type, version
FROM   (SELECT id, type, version, 
               RANK() OVER (PARTITION BY type ORDER BY version DESC) AS rk
        FROM   agreement) t
WHERE  rk = 1

Answer 2

It should work:

 select max(id) , type , max(version) from Agreement
 group by type

Answer 3

Try this:

SELECT a.id, a.type, a.version
FROM Agreement AS a
INNER JOIN (SELECT type, MAX(version) AS maxV
            FROM Agreement
            GROUP BY type) t 
ON t.type = a.type AND a.version = t.maxV

This query uses a derived table containing the MAX(version) per type. Joining to this derived table we can get all rows of the original table having the maximum version per type.

Demo here

Answer 4

Postgresql:

select * from agreement a where id in (select id from agreement b where a.type=b.type order by id desc limit 1)

Result:

2 | Terms | 2

3 | Privacy | 1