From a question on Freenode.
SELECT * FROM theTable;
+----+----------+ | id | data | +----+----------+ | 1 | middle | | 2 | first | | 3 | showLast | +----+----------+
Fair enough. Let’s order it by `data`.
SELECT * FROM theTable ORDER BY data;
+----+----------+ | id | data | +----+----------+ | 3 | showLast | | 1 | middle | | 2 | first | +----+----------+
What??? Maybe the server didn’t quite understand.
SELECT * FROM theTable ORDER BY data ASC;
+----+----------+ | id | data | +----+----------+ | 3 | showLast | | 1 | middle | | 2 | first | +----+----------+
How did that happen? I assure you I have not switched to some ‘reverse order’ collation.
SHOW CREATE TABLE theTable;
CREATE TABLE `thetable` (
`id` int(11),
`data` enum('showLast','middle','first')
)
Ah. `data` is an enum. ORDER BY on an enum orders by the enum index, not the string. You can order by CONCAT(data) or by CAST(data AS char). Are there better ways?
Leave a Reply