CODEWITHSUNDEEP
mysqlsql-order-by
Chirag
Chirag

Reputation: 1929

MySQL Sorting number issue

I have few number sets that I'm trying to word

4.13.1.5, 4.13.1, 4.10.5, 4.9.97

But when I use mysql order by function somehow it sorts out in an odd way. 

4.13.1.5, 4.13.1, 4.9.37, 4.10.5

Should it be that 4.9 comes at the end? Am I missing something here?

Upvotes: 0

Views: 202

Answers (2)

Brad Christie
Brad Christie

Reputation: 101614

Since these are technically strings and not numbers, MySQL is doing the correct thing. The . (character 46) is higher in sort order than any of the numbers (characters 48-59). When it comes to comparing the string, it's going by ASCII value not breaking it down in to tokens and comparing the numbers.

Upvotes: 2

krakover
krakover

Reputation: 3029

Works for me: 

mysql> create table test ( value varchar(10));
Query OK, 0 rows affected (0.18 sec)


mysql> insert into test values ("4.13.1.5"), ("4.13.1"), ("4.10.5"), ("4.9.97")
    -> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select value from test order by value;
+----------+
| value    |
+----------+
| 4.10.5   |
| 4.13.1   |
| 4.13.1.5 |
| 4.9.97   |
+----------+
4 rows in set (0.00 sec)

Upvotes: 0

Related Questions