MySQL – ORDER BY does not sort data properly…
Imagine a developer working late at night trying to complete some simple queries for a report due the next day. No matter what she does, the resulting query will just not sort properly. Is this some undiscovered MySQL bug? No wonder this software is free, MySQL is broke and can’t even perform a basic sort. It looks simple, sort the data in a table:
mysql> SELECT id, technology from Enigma;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+
To sort the records based on the technology, do an ORDER BY on column Technology:
mysql> SELECT id, technology from Enigma ORDER BY Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+
It seems that the server does not understand what I have asked, or I have not mastered the entire alphabet …
Let’s add the clause CSA (just in case!)
mysql> SELECT id, technology from Enigma ORDER BY ASC Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+
That does not seem to be any better! MySQL does not sort the data as I wish, am I going crazy? Should I change my RDBMS?
Don’t worry, a careful look at the table structure allows us to see things clearly.
mysql> SHOW CREATE TABLE enigma;
Table: enigma
Create Table: CREATE TABLE `Enigma` (
`Id` int (11) DEFAULT NULL,
`Technology` enum (‘PHP’, ‘Linux’, ‘MySQL’) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1
The column technology is of a type ENUM. The Enum field is stored as an integer, and that is the index that MySQL uses for sorting. In other words, 1 is PHP, 3 is MySQL, and 2 is Linux, so sorting does works properly, just not as expected when the Enum data field is storing character data. So how do you get the desired result? Simply force MySQL to use the value string and not index when sorting. This can be done by using either the Concat() or Cast() functions, as follows:
function concat ()
mysql> SELECT id, technology from Enigma ORDER BY concat (technology);
+——+————-+
| Id | technology |
+——+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+————-+
function cast ()
mysql> SELECT id, technology from Enigma ORDER BY cast (technology as char);
+——+————-+
| id | Technology |
+—– -+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+———– – +
- B-17 E-Z Goin’ and the Sonderkommando Elbe – Buchen Raid
- The Rolls-Royce Merlin – Could it be the best piston engine ever?
If I didn’t know any better I’d think you were psychic.
I’m a developer working late doing EXACTLY what you said. Thanks for the fix, you have saved me a lot of confusion. I had forgotten that the field I was dealing with was an enum string set.
Thanks for the insight. Saved a lot of headache!
Thanks for the fix. Helped me a lot. Awesome hacks
Also the problem that I had with sorting numbers was that I put the $variable within single quotes. Just thought I’d chime in in case someone has the same issue I had.