Grab two latest records of group by platform and semver

Question

For every pairing of platform and semver I'm trying to to get the second newest record as a way to support the previous app version to latest, at all times.

Here's my schema:

id, platform, semver, name

Some sample data:

I would like the query to return:

papapal, ios,     1.0.2
papapal, android, 1.0.2
foobar,  ios,     1.4.0
foobar,  android, 1.4.0

Notice how it's not the latest record, but the second latest record.

I can sort by semver using, but the grabbing second latest for each app is tripping me up.

SELECT id, semver
FROM   app_versions
ORDER  BY string_to_array(semver, '.')::int[];

Appreciate any help!

Answer 1

If the nubers between dots always are one digit only, you can use window functions as follows:

select platform, name, semver
from (
    select
        t.*,
        row_number() over(partition by platform, name order by semver desc) rn
    from mytable t
) t
where rn = 2

On the other hand, if you have versions numbers like '1.12.2' (which is greater than '1.2.1'), then you can indeed turn them to arrays of integers for ordering:

select platform, name, semver
from (
    select
        t.*,
        row_number() over(partition by platform, name order by string_to_array(semver, '.')::int[] desc) rn
    from mytable t
) t
where rn = 2