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]
