trino sql group by and sort semantic versioning data

Viewed 26

I am trying to run a following query in trino engine to group data by certain fields and get recent version value for that group.

The version field is of varchar datatype.

A resultset eg.

appid    p  version
___________________________
4ef36d   1  3.4.0-mvn.1
4ef36d   1  3.4.0-beta.1
4ef36d   1  3.4.0
4ef36d   1  3.3.7-beta-1
4ef36d   1  3.3.7
4ef36d   1  3.3.5

I tried using max agg. function to do the job but this only evaluates the values in alphabetic order and length, hence gives 3.4.0-mvn.1 as the recent one.

select appid, p,max(version) as latest_version
FROM session
WHERE appid = '4ef36d'
    AND platform = 1
group by appid, p

Output:

appid   p   latest_version
-------------------------------
4ef36d  1   3.4.0-mvn.1

Is there any way I can handle/sort these semantic versions?

0 Answers
Related