ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.)
For example, 'a' sorts before 'b' for ENUM('a', 'b'), but 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetic order. You can also use ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col) to make sure that the column is sorted lexically rather than by index number.
See the screen below when you try to do ORDER BY on an ENUM data type
Select distinct(continent) as c from COUNTRY order by c;
Here is the fix for MySQL ENUM ORDER BY clause
Select distinct(continent) as c from COUNTRY order by concat(c);
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.