Blog Archive

MySQL ORDER BY for ENUM data type

How to sort MySQL enum data in logical ascending or descending order by using ORDER BY clause? An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

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;

MySQL ORDER BY for ENUM data type


Here is the fix for MySQL ENUM ORDER BY clause

Select distinct(continent) as c from COUNTRY order by concat(c);

MySQL ORDER BY for ENUM data type