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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
