Sorting Query Results - MySQL - page 268

Then select the values, both as strings and as the internal numeric value (obtain the
latter using +0 to force a string-to-number conversion):
mysql> SELECT day, day+0 FROM weekday;
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Monday | 2 |
| Friday | 6 |
| Tuesday | 3 |
| Sunday | 1 |
| Thursday | 5 |
| Saturday | 7 |
| Wednesday | 4 |
+-----------+-------+
Notice that because the query includes no ORDER BY clause, the rows are returned in
unsorted order. If you add an ORDER BY day clause, it becomes apparent that MySQL
uses the internal numeric values for sorting:
mysql> SELECT day, day+0 FROM weekday ORDER BY day;
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
+-----------+-------+
What about occasions when you want to sort ENUM values in lexical order? Force them
to be treated as strings for sorting using the CAST() function:
mysql> SELECT day, day+0 FROM weekday ORDER BY CAST(day AS CHAR);
+-----------+-------+
| day | day+0 |
+-----------+-------+
| Friday | 6 |
| Monday | 2 |
| Saturday | 7 |
| Sunday | 1 |
| Thursday | 5 |
| Tuesday | 3 |
| Wednesday | 4 |
+-----------+-------+
If you always (or nearly always) sort a non-enumeration column in a specific nonlexical
order, consider changing the data type to ENUM , with its values listed in the desired sort