ORDER BY "ENUM field" in MYSQL
enums, mysql, sql, sql-order-by
Solution
As documented under Sorting:
`ENUM` values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, `'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 when using the `ORDER BY` clause on an `ENUM` column, use one of these techniques:
Specify the `ENUM` list in alphabetic order.
Make sure that the column is sorted lexically rather than by index number by coding `ORDER BY CAST(col AS CHAR)` or `ORDER BY CONCAT(col)`.
Per the second bullet, you can therefore sort on the column after it has been cast to a string:
ORDER BY CAST(noticeBy AS CHAR)
Problem
There is a field 'noticeBy' enum('email','mobile','all','auto','nothing') NOT NULL DEFAULT 'auto'. As it known ordering by ENUM field performs relative to its index. However, how it possible make order by its values?