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

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.

Answers


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

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


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


Need Your Help

Select tag with many items takes time to render

ruby-on-rails ruby view rendering

I have a form with a select tag containing nearly 17k items.

Understanding how android app is installed by PackageManagerService

android android-source android-permissions android-install-apk android-package-managers

So I've been digging around AOSP trying to figure out how an android app is installed. So far this is where I'm at: