Mysql query trivia

Question

Updated: Please assume, I will have exactly 4 elements in my version number x.x.x.x

Table xyz (id int, os varchar, version varchar)

id      os       version
1       mac      1.0.0.4
2       android  1.0.1.2
3       mac      1.0.0.14
4       mac      1.0.0.07

I want to find the maximum version number of each operating system.

select max(version), os from xyz group by os

but in the above data sample, it returns 1.0.0.4 as the highest version of mac, rather than returning 1.0.0.14. Is there any way to fix the above query? I know version is varchar :( If there is NO solution possible, then I can change datatype to other, but that will still not solve the issue.

Answer 1

If the 4 parts in the version are all numbers and not bigger than 255, you can use the INET_ATON() function, and its reverse INET_NTOA():

SELECT INET_NTOA(MAX(INET_ATON(version))), os 
FROM xyz 
GROUP BY os ;

Tested in SQL-Fiddle

Answer 2

Mysql uses text sort for this field when you try to get max:
SELECT version FROM test ORDER BY VERSION;
1.0.0.14
1.0.0.4
1.0.07
1.0.1.2.4

But even if you try to cast this to integer, you will get
SELECT version FROM test ORDER BY CAST(VERSION AS SIGNED);
1
1
1
1

And this really has a sense. How do you want MySQL to guess, what you want? MySQL should be used to store data, and you should format it yourself.

A fine solution would be to use several fields for version:
id int, os varchar, versionMajor int, versionMinor int, versionMoreMinor int, versionEvenMoreMinor int

You should be able to sort and to format them as you wish.