MySQL Order with decimals

Question

I've got a problem with mysql ordering. My query:

SELECT * FROM versions ORDER BY version DESC

It lists some of the versions like this:

25.0.1364.86
25.0.1364.124
23.0.1271.100

however .124 is higher than .86.

How can I fix it?

Answer 1

A small improvement to the query posted from @peterm (with thanks!)

SELECT *
FROM `versions`
ORDER BY 1*SUBSTRING_INDEX(version, '.', 1) asc, 
     1*SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', -3),'.', 1) asc, 
     1*SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', -2),'.', 1) asc, 
     1*SUBSTRING_INDEX(version, '.', -1) asc,
     version asc # this will sort non-numeric strings

I tested with more complicated values, letters, numbers, dashes and points, as versions can be written in any format.

| version |
-----------
| a-b     |
| a-c     |
| ab      |
| b       |
| c       |
| c.a     |
| c.a.b   |
| c.b     |
| c.b.a   |
| c.b.b   |
| ca      |
| 1.2     |
| 1.2.1   |
| 2.1.1   |
| 2.1.2   |
| 3       |
| 10      |
| 123     |

Answer 2

If the format of version column is fixed then you can split the version into parts and ORDER BY them.

SELECT *
FROM versions
ORDER BY 1*SUBSTRING_INDEX(version, '.', 1) DESC, 
         1*SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', -3),'.', 1) DESC, 
         1*SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', -2),'.', 1) DESC, 
         1*SUBSTRING_INDEX(version, '.', -1) DESC

Output:

|       VERSION |
-----------------
| 25.0.1364.124 |
| 25.0.1364.86  |
| 23.0.1271.100 |

SQLFiddle