Distinct latest versions in mysql

Question

I have a column in my mysql table something like this:

appVersion

5.16.0.2889
5.16.0.2893
5.14.0
5.14.0.3
5.12.0.1
5.15.0.14
5.1.0.2
5.12.0.3

I want to get the distinct latest versions of this column segregated on the basis of the first two numbers (a.b.c.d then only consider a.b as a single version)

like here we have let’s say the 3 distinct latest versions would be:

5.16.0.2893
5.16.0.2889
5.15.0.14
5.14.0.3
5.14.0

can someone please suggest mysql query for this?

definition of latest: let’s say if we have

5.14.0.9
5.12.0.4
6.1.0.2
5.14.0.12
5.16.1.2
5.12.1.1

distinct latest 3 would be

6.1.0.2
5.16.1.2
5.14.0.12
5.14.0.9

let's say if we have > 5.14.0.9 > 5.12.0.4 > 6.1.0.2 > 5.14.0.12 > 5.16.1.2 > 5.12.1.1 latest 4 would be > 6.1.0.2 > 5.16.1.2 > 5.14.0.12 > 5.14.0.9 — batman, Mar 19 '20 at 15:00
The two examples are a bit different. The new one can be solved very simply, but if the original one is really plausible, then we have to be a bit more inventive. — Strawberry, Mar 19 '20 at 15:04
I see in your sample data a value `5.14.0`. What is the difference with a value like `5.14.0.0`? Also what is your MySql version? — forpas, Mar 19 '20 at 15:23
@forpas there's no difference between 5.14.0 and 5.14.0.0, both are same mysql version - 5.7.29 — batman, Mar 19 '20 at 15:28
Do you always have max 4 parts in the version number? Just convert it to 4 numbers and sort it starting from the left most. — James Z, Mar 19 '20 at 19:51

Prakhar Londhe · Accepted Answer · 2020-03-21T20:39:04.873

What I understand from your question, you need to display 3 latest "a.b" kind of versions, but with all it's subversions. Here is the code that will work :

     select ver from app inner join 
  (select CONCAT(LPAD(SUBSTRING_INDEX(ver, '.', 1), 50, '0'),
LPAD(SUBSTRING(ver, LOCATE('.', ver)+1, LOCATE('.', ver, LOCATE('.', ver)+1)), 50, '0')) as ver2
from app group by ver2 order by ver2 desc limit 3) as v2 
on CONCAT(LPAD(SUBSTRING_INDEX(ver, '.', 1), 50, '0'),
LPAD(SUBSTRING(ver, LOCATE('.', ver)+1, LOCATE('.', ver, LOCATE('.', ver)+1)), 50, '0')) = v2.ver2 
order by v2.ver2 desc

The inner query spits out the top 3 versions of the form 'a.0000b', and the inner join matches all the versions to 'a.b' = 'c.d'

score 0 · Answer 2 · edited May 12 '20 at 13:25

0

SELECT VersNo
FROM (
SELECT  DISTINCT SUBSTRING_INDEX(appVersion,'.',2) AS 'VersionNo',appVersion AS 'VersNo'
FROM table
ORDER BY appVersion)s
ORDER BY VersNo;

edited May 12 '20 at 13:25

Nimantha

6,405
6
28
69

answered Mar 21 '20 at 09:26

Siva Koteswara Rao

129
1
6

Please explain your answer, this will make it more accessible. – Jon Scott May 12 '20 at 17:49

Distinct latest versions in mysql

2 Answers2