I am using the test data "bank" to study mysql on mac. I have a question about the alphabet sort in mysql.
I have a example codeselect cust_id,cust_type_cd,city,state,fed_id from customer order by 2 asc;
The return shows in column 2, "I" is before "B".
Anyone knows what is the reason? Many thanks.
I would guess that cust_type_cd
is an ENUM
column with "I" ordered before "B" in the enum definition.
Enums sort by the ordinal position of the value in the list defined by the enumeration, not by the alphabetical value.
To sort alphabetically, either define the enum with entries in alphabetical order, or else force the value to be converted to its string value:
... ORDER BY CONCAT(cust_type_cd) ASC
See also http://dev.mysql.com/doc/refman/5.6/en/enum.html#enum-sorting
Note that using a function like that in the ORDER BY
clause spoils any chance of using an index for sorting. It will be forced to use a filesort.