Select all records of type 'x' that are the highest 'version'
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.
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
It should work:
select max(id) , type , max(version) from Agreement group by type
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.
select * from agreement a where id in (select id from agreement b where a.type=b.type order by id desc limit 1)
2 | Terms | 2
3 | Privacy | 1