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?