CODEWITHSUNDEEP
mysqlinnodb
jskidd3
jskidd3

Reputation: 4783

MySQL ORDER BY string as number (larger numbers above smaller numbers)

Consider the following results where code is type VARCHAR:

SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, `code

|code|
|4   |  
|420 |
|5   |
|T6  |
|X30 |

How can I change the query so that it returns the results in this order:

|code|
|4   |  
|5   |
|420 |
|T6  |
|X30 |

Upvotes: 4

Views: 121

Answers (3)

Yusuf Kalsen
Yusuf Kalsen

Reputation: 9

LENGTH() function in SQL is used to get the length of a string:

SELECT code
FROM lock
ORDER BY LENGTH(code), code

OUT:

+------+
| code |
+------+
| 4    |
| 5    |
| T6   |
| 420  |
| X30  |
+------+

Upvotes: -1

Alexander
Alexander

Reputation: 20224

SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, CAST(code AS signed) ASC, code ASC

The first order will sort the numbers to the front. The second order will only sort the numbers ascending, while the first will keep them at the start. The third will only sort the strings ascending, the numbers will keep their order because they are already sorted.

Upvotes: 5

Strawberry
Strawberry

Reputation: 33945

E.g.:

SELECT *
     , code+0 
     , code+0=0
  FROM (SELECT '4' code UNION 
        SELECT '420'    UNION 
        SELECT '5'      UNION 
        SELECT 'T6'     UNION 
        SELECT 'X30') n 
 ORDER 
    BY code+0=0
     , code+0
     , code;

+------+--------+----------+
| code | code+0 | code+0=0 |
+------+--------+----------+
| 4    |      4 |        0 |
| 5    |      5 |        0 |
| 420  |    420 |        0 |
| T6   |      0 |        1 |
| X30  |      0 |        1 |
+------+--------+----------+

Upvotes: 0

Related Questions