Geek asks and answers

MySQL order by (str to int)

SELECT `01` FROM perf WHERE year = '2013' order by CAST(`01` AS INT) LIMIT 3

Column 01 has numerical values ​​like varchar. I need to order the top of '01' as a whole. Why does this query not work?

The table is as follows:

+----------------------+
| name | 01 | 02 | year|
+----------------------+
|name1 | 90 |*** |2013 |
+----------------------+
|name2 | 93 | 55 |2013 |
+----------------------+
|name3 |*** | 78 |2013 |
+----------------------+

The request must be ordered by 01 (dismiss *) and give names and values.

+5
source share
1 answer

MySQL does not allow you CAST('01' AS INT). He expects instead SIGNEDor UNSIGNED.

SELECT `01` FROM perf WHERE year = '2013' order by CAST(`01` AS SIGNED) LIMIT 3

Browse the MySQL docsCAST() for full details.

mysql> SELECT CAST('01' AS SIGNED);
+----------------------+
| CAST('01' AS SIGNED) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

, CASE ORDER BY, . , 01 0, SIGNED 0, - , .

, , , 999999999 ORDER BY, . name.

SELECT * FROM perf 
WHERE year = '2013'
ORDER BY
  CASE WHEN (`01` <> '0' AND CAST(`01` AS SIGNED) <> 0) THEN CAST(`01` AS SIGNED) ELSE 999999999 END,
  name
LIMIT 3

http://sqlfiddle.com/#!2/846e2/6

, ()

  CASE WHEN (`01` <> '0' AND CAST(`01` AS SIGNED) <> 0) THEN CAST(`01` AS SIGNED) ELSE -999999999 END DESC,
+17

More articles:


All Articles