CODEWITHSUNDEEP
mysqlsql-order-by
Skylineman
Skylineman

Reputation: 576

MySQL Order with decimals

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?

Upvotes: 3

Views: 4460

Answers (5)

Walker Boh
Walker Boh

Reputation: 770

Late to the party but there is a much easier answer to this using the INET_ATON() function.

SELECT * FROM versions ORDER BY INET_ATON(version)

will output

23.0.1271.100
25.0.1364.86
25.0.1364.124

Read more about INET_ATON() here: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

Upvotes: 1

igasparetto
igasparetto

Reputation: 1146

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     |

Upvotes: 3

peterm
peterm

Reputation: 92785

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

Upvotes: 2

Kermit
Kermit

Reputation: 34055

Here's my approach (hope you don't mind I blogged about it):

SELECT v.version 
FROM   (SELECT version, 
               Cast(Substring_index(version, '.', 1) AS UNSIGNED INTEGER)  major, 
               Cast(Substring_index(Substring_index(version, '.' 
                   , 2 ), '.', -1) AS UNSIGNED INTEGER)                    minor, 
               Cast(Substring_index(Substring_index(version, '.' 
                   , -2 ), '.', 1) AS UNSIGNED INTEGER)                    patch, 
               Cast(Substring_index(version, '.', -1) AS UNSIGNED INTEGER) build 
        FROM   versions) v 
ORDER  BY v.major, 
          v.minor, 
          v.patch, 
          v.build

Result

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

See the demo

Upvotes: 0

Chris K
Chris K

Reputation: 550

Without knowing how the table versions is defined it is difficult to answer for sure, but that looks like it is being searched as text, in which case 86 is greater than 124 (think of dictionary ordering). One simple solution might be to store the data in two formats in your table - keep the string you seem to have, and have a decimal equivalent, for example 25.0.1364.86 as a string and 2501364.86 as a decimal. This would ensure your ordering worked as expected.

Upvotes: 0

Related Questions