mysql sorting of version numbers

Question

I have values like:

1.1.2 
9.1 
2.2
4
1.2.3.4
3.2.14
3.2.1.4.2
.....

I need to sort those values using mysql. The data type for this one is varbinary(300).

The desired output will be like:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1

The Query is:

select version_number from table order by version_number asc

it does not give the correct sorting order.

The desired output of this is:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14 
4
9.1

The version numbers are up to 20 digits (like 1.2.3.4.5.6.7.8.9.2.34) and more also. There is no particular max size and the standard version is just like above mentioned.

In ascending order, which do you expect to come first `1.2.13` or `1.2.2`? — mal-wan, Sep 22 '11 at 00:49
Can you post your output and point out the issue (actual vs. desired)? — Michael M., Sep 22 '11 at 00:53
Mostly you store this version_number in Varchar2, so you can't perform sorting like this. — Thinhbk, Sep 22 '11 at 00:53
Is there a maximum known size of each component? Can you store a standardized version (such as `011.023.005` instead of `11.23.5`) in a separate column just for sorting purposes? — mu is too short, Sep 22 '11 at 02:28

score 54 · Answer 1 · answered Dec 06 '11 at 23:21

Try abusing the INET_ATON function to do the sorting like so:

SELECT version_number FROM table ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version_number,'.0.0.0'),'.',4))

This trick was originally posted on the mysql mailing list, so many thanks to the original poster, Michael Stassen!

Here's what he had to say:

If each part is no larger than 255, you can leverage INET_ATON() to do what you want (up to the 4th part). The trick is making each of these look like an IP first by using CONCAT to add '0.0.0' to make sure every row has at least 4 parts, then SUBSTRING_INDEX to pull out just the first 4 parts.

Now, I must point out that because we are sorting on a function of the column, rather than on the column itself, we cannot use an index on the column to help with the sort. In other words, the sorting will be relatively slow.

In the latter case, he recommends a solution similar to the one posted by @spanky (separate columns).

also works with single version numbers (where 0.9 < 0.10) – Tadas V. Feb 10 '22 at 21:10 — Tadas V., Feb 10 '22 at 21:10

score 7 · Answer 2 · answered Sep 22 '11 at 01:38

7

I would store it in three separate columns, one for each part of the version number.

Make each column a TINYINT and even create an index across the 3 columns. That should make things simple.

Then you can do: select CONCAT(v1,'.',v2,'.',v3) AS version_number FROM table ORDER BY v1 asc, v2 asc, v3 asc

answered Sep 22 '11 at 01:38

spanky

1,479
2
11
22

1

That's going to get pretty horrendous with 20 components in the version number. – mu is too short Sep 22 '11 at 02:26
you could replicate your versions as a nested set model in that case, so you can make it grow in all directions without ever adding columns to your table. but implementing that is probably quite a hassle. – iHaveacomputer Sep 22 '11 at 02:45
You might want to consider `select concat('1', '.', '2', '.', null);` as well and then think about 20 columns again. – mu is too short Sep 22 '11 at 03:13
2

Yeah when I posted this, the question didn't include the fact that there are 20 digits, and it looked like just 3 or 4 digits. It appears that no one else posted an answer. I, for one, would like to know why anyone would use 20 pieces of a version number. I can't even envision a scenario. But to help, I recommend doing a fixed-width standardized version like @muistooshort posted. – spanky Sep 27 '11 at 07:07

vovafeldman · Answer 3 · 2017-01-16T23:00:14.687

If you'd like to support versions like 1.1-beta or using old MySql versions without INTE_ATON, you can get the same sort by splitting the version and sorting each part as an integer and string:

SELECT
    version,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 1), LENGTH(SUBSTRING_INDEX(version, '.', 1 - 1)) + 1), '.', '') v1,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 2), LENGTH(SUBSTRING_INDEX(version, '.', 2 - 1)) + 1), '.', '') v2,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 3), LENGTH(SUBSTRING_INDEX(version, '.', 3 - 1)) + 1), '.', '') v3,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 4), LENGTH(SUBSTRING_INDEX(version, '.', 4 - 1)) + 1), '.', '') v4
FROM 
    versions_table
ORDER BY
    0+v1, v1 DESC, 0+v2, v2 DESC, 0+v3, v3 DESC, 0+v4, v4 DESC;

score 4 · Answer 4 · answered May 06 '21 at 22:32

4

Use regular expressions. First normalize the value:

SELECT REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE('v1.22.333', '^v', ''),
        '(^|\\.)(\\d+)',
        '\\100000\\2'
    ),
    '0+(\\d{5})(\\.|$)',
    '\\1\\2'
)

Output:

00001.00022.00333

Then you can sort normally.

This solution works with any number of components. You can scale component length from 5 to any fixed length.

answered May 06 '21 at 22:32

Dávid Horváth

4,050
1
20
34

With MySQL 8.0.32 I had to use `$` instead of ``\\`` for the back references (e.g. `$1` instead of ``\\1``). – user1191247 Mar 18 '23 at 13:30

mysql sorting of version numbers

4 Answers4

Linked

Related