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)

Improve this question
1

6 Answers 6

Reset to default
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)
;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

glad it helped! Could you accept the answer to indicate that it answered your question, please?
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.

Improve this answer

3 Comments

holly crap that worked like a charm, i was trying to solve this for ages
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.
@WajiraWeerasinghe No. That's an answer to a subtly different problem.
2

It may help, Simple and Exact Answer:

SELECT `COLUMN_NAME` FROM `TABLENAME` WHERE 1 ORDER BY `COLUMN_NAME` *1
Improve this answer

Comments

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.

Improve this answer

Comments

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.

Improve this answer

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.