List:General Discussion« Previous MessageNext Message »
From:Johan Höök Date:April 29 2005 7:12am
Subject:Re: order by "version number"
View as plain text  
Hi Stano,
there was a response by Michael Stassen on the list about a year ago on
this problem, which I hope he doesn't mind if I repeat below.
/Johan
Version numbers?

   CREATE TABLE ss (version VARCHAR(13));
   INSERT INTO ss VALUES ('1'),('1.1'),('1.2.1.2'),('1.10.1'),('1.2'),
                         ('1.4.1'),('2.1'),('2.2.1.2'),('2.10.1'),('2.4.1');

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.

   SELECT version FROM ss
   ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version,'.0.0.0'),'.',4));

+---------+
| version |
+---------+
| 1       |
| 1.1     |
| 1.2     |
| 1.2.1.2 |
| 1.4.1   |
| 1.10.1  |
| 2.1     |
| 2.2.1.2 |
| 2.4.1   |
| 2.10.1  |
+---------+
10 rows in set (0.00 sec)

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.

One solution would be to separate the parts into separate columns, each 
of which could then be an appropriately sized integer (TINYINT, 
perhaps).  You could use 0 or NULL for the missing parts, as you see fit 
(NULLS preserve the output format you specified, e.g. 1.1, but 
complicate matching).

   CREATE TABLE ss2 (v1 TINYINT UNSIGNED, v2 TINYINT UNSIGNED,
                     v3 TINYINT UNSIGNED, v4 TINYINT UNSIGNED,
                     KEY version_idx (v1,v2,v3,v4));

   INSERT INTO ss2 VALUES (1,NULL,NULL,NULL),(1,1,NULL,NULL),(1,2,1,2),
 
(1,10,1,0),(1,2,0,0),(1,4,1,0),(2,1,0,0),(2,2,1,2),
                          (2,10,1,NULL),(2,4,1,NULL);

Glue the parts together with CONCAT_WS() when you select them, and sort 
by all 4 parts:

   SELECT CONCAT_WS('.',v1,v2,v3,v4) version FROM ss2 ORDER BY v1,v2,v3,v4;
+----------+
| version  |
+----------+
| 1        |
| 1.1      |
| 1.2.0.0  |
| 1.2.1.2  |
| 1.4.1.0  |
| 1.10.1.0 |
| 2.1.0.0  |
| 2.2.1.2  |
| 2.4.1    |
| 2.10.1   |
+----------+
10 rows in set (0.00 sec)

In this case, the multicolumn index on the 4 parts will be used to sort.

Another option would be to use INET_ATON() when storing the values.

   CREATE TABLE ss3 (v INT UNSIGNED, KEY version_idx (v));

In this case, you'd have to use 0 for missing parts.

   INSERT INTO ss3 VALUES (INET_ATON('1.0.0.0')), (INET_ATON('1.1.0.0')),
                          (INET_ATON('1.2.1.2')), (INET_ATON('1.10.1.0')),
                          (INET_ATON('1.2.0.0')), (INET_ATON('1.4.1.0')),
                          (INET_ATON('2.1.0.0')), (INET_ATON('2.2.1.2')),
                          (INET_ATON('2.10.1.0')),(INET_ATON('2.4.1.0'));

Use INET_NTOA() when selecting to display dotted numbers, but sort by 
the values already in the column.

   SELECT INET_NTOA(v) version FROM ss3 ORDER BY v;

+----------+
| version  |
+----------+
| 1.0.0.0  |
| 1.1.0.0  |
| 1.2.0.0  |
| 1.2.1.2  |
| 1.4.1.0  |
| 1.10.1.0 |
| 2.1.0.0  |
| 2.2.1.2  |
| 2.4.1.0  |
| 2.10.1.0 |
+----------+
10 rows in set (0.00 sec)

As with the second version, the index on v will be used for the sort.

Finally, starting with mysql 4.1.2, INET_ATON() will assume 0s for 
missing parts, so long as you have at least two parts.  That is, 1.1 
will automatically be treated as 1.1.0.0, 1.0 as 1.0.0.0, and 1.2.3 as 
1.2.3.0 (but 1 will be treated as 0.0.0.1).  With 4.1.2 then, the above 
could be simplified slightly.

Michael


Stano Paska wrote:
> Hi,
> 
> in my table I have one varchar(20) column where I store version number.
> Version looks like:
> 1.1.2
> 1.2.1
> 1.10.3
> It is possible order this column in natural order (1.2 before 1.10)?
> 
> Stano.
> 

Thread
order by "version number"Stano Paska29 Apr
  • Re: order by "version number"Johan Höök29 Apr
  • RE: order by "version number"mathias fatene29 Apr
Re: order by "version number"Dusan Kolesar29 Apr