Menu
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?
586
If this type is specified (in a table definition), numbers can be in an arbitrary order. However, the order does not matter. Neither the string nor the numeric value in an Enum can be NULL.
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.
An ENUM is a string object whose value is decided from a set of permitted literals(Values) that are explicitly defined at the time of column creation. Succinct data storage required to store data in limited size columns.
As documented under Sorting:
ENUMvalues 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'forENUM('b', 'a'). The empty string sorts before nonempty strings, andNULLvalues sort before all other enumeration values.To prevent unexpected results when using the
ORDER BYclause on anENUMcolumn, use one of these techniques:
Specify the
ENUMlist in alphabetic order.Make sure that the column is sorted lexically rather than by index number by coding
ORDER BY CAST(col AS CHAR)orORDER 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
