2

i have a column with name title with rows :

  • 1.8.8
  • 1.8.9
  • 1.9.1
  • 1.9.2
  • 1.8.10

and I need sort like this

  • 1.8.8
  • 1.8.9
  • 1.8.10
  • 1.9.1
  • 1.9.2

is any way how to make it? (type of column is varchar)

Tomáš Trúsik
  • 97
  • 1
  • 8
  • http://stackoverflow.com/questions/5471237/sorting-ordering-problem-in-mysql/5471419#5471419 – Devart Nov 22 '13 at 11:29

6 Answers6

6

Clunky, but should work provided all of your entries are in the format x.x.x:

select yourColumn
from yourTable
order by
cast(substring_index(yourColumn,'.',1) as unsigned),
cast(substring_index(substring_index(yourColumn,'.',2),'.',-1) as unsigned),
cast(substring_index(substring_index(yourColumn,'.',3),'.',-1) as unsigned)
;
Tom Mac
  • 9,693
  • 3
  • 25
  • 35
  • glad it helped! Could you accept the answer to indicate that it answered your question, please? – Tom Mac Nov 22 '13 at 10:58
3
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table(legal VARCHAR(12) NOT NULL PRIMARY KEY);

INSERT INTO my_table VALUES('1.8.8'),('1.8.9'),('1.9.1'),('1.9.2'),('1.8.10');

SELECT * FROM my_table ORDER BY INET_ATON(legal);

+--------+
| legal  |
+--------+
| 1.8.8  |
| 1.8.9  |
| 1.8.10 |
| 1.9.1  |
| 1.9.2  |
+--------+

Note that this is a hack. In consequence, it has certain limitations. That said, there's a high likelihood that it will work fine for your purposes.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • holly crap that worked like a charm, i was trying to solve this for ages – norbertas.gaulia May 11 '14 at 23:08
  • Yes this only works for the format of 255.255.255.255, it will not work for numbers beyond 255. Should be carefully used. I suggest to use Abdul Rehman's solution in this post which is ORDER BY `COLUMN_NAME` *1 worked for very well in my case. – Wajira Weerasinghe Jun 10 '19 at 13:24
  • @WajiraWeerasinghe No. That's an answer to a subtly different problem. – Strawberry Jun 10 '19 at 13:33
2

It may help, Simple and Exact Answer:

SELECT `COLUMN_NAME` FROM `TABLENAME` WHERE 1 ORDER BY `COLUMN_NAME` *1
Abdul Rehman
  • 689
  • 6
  • 11
0

This is called "natural sort" and there is no way to perform natural sort in MySQL: Natural Sort in MySQL

You can solve it by using special sorting field. With values like:

10808 10809 10901 10902 10910 (leading zeros)

PS as a bonus - sorting integers works muuuuch faster than sorting strings (especially strings with some magic rules). Especially if you create sorting index.

Community
  • 1
  • 1
Stalinko
  • 3,319
  • 28
  • 31
0

none of the answer quite were practical(either wrong or not performant). Let's think your varchar column with dots is productId then you must use:

SELECT * FROM PRODUCT_TABLE ORDER BY productId * 1,length(productId)

productId * 1 gives you the same result as INET_ATON(productId) and they both are incomplete.

0.0.0
0.0
0

I got a result like that so to fix that just added length(productId).

0
0.0
0.0.0

and now everything is fine.

Steve Moretz
  • 2,758
  • 1
  • 17
  • 31
0

Slight different problem. I have a lists that looks like as follows:

  • 1
  • 1.1
  • 1.2
  • ...
  • 1.10
  • 1.10.1
  • 1.11

I resolved my problem in MariaDB using REGEXP_REPLACE inserting leading "0":

ORDER BY REGEXP_REPLACE(kKey,'.([[:<:]][1-9])[[:>:]]', '.0\1');

I have a maximum of 2 digits after "." So I replace all dots followed by a single number ([[:<:]][1-9])[[:>:]] between word boundaries and capture the number. It is then replaced with '.0' + captured number.

Damien Goor
  • 81
  • 3