Geek Answers Handbook

MySQL ORDER BY string as a number (large numbers above lower numbers)

Consider the following results, where codeis the type VARCHAR:

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

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

How can I modify the query so that it returns results in the following order:

|code|
|4   |  
|5   |
|420 |
|T6  |
|X30 |
+4
source share
3 answers
SELECT code
FROM lock
ORDER BY CAST(code AS signed) > 0 DESC, CAST(code AS signed) ASC, code ASC

First order sorts the numbers in front. The second order will only sort the numbers ascending, while the first will keep them at the beginning. The third one will sort the lines only in ascending order, the numbers will keep their order, because they are already sorted.

+5
source

eg:.

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 |
+------+--------+----------+
0
source
Function

LENGTH() 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  |
+------+
-1
source

More articles:


All Articles