2

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.

Kenny
  • 355
  • 1
  • 5
  • 14
  • 1
    It's your `order by` ... you say, `order by 2` The asc/desc won't matter b/c 2 is always equal to itself, so then it defaults to how the rows are structured in the underlying file. –  Jun 09 '14 at 07:11
  • 1
    @JeremyMiller, "Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1." http://dev.mysql.com/doc/refman/5.6/en/select.html – Bill Karwin Jun 09 '14 at 07:19
  • @BillKarwin Super sweet. I love learning stuff I hadn't known before. Thanks. –  Jun 09 '14 at 07:20
  • 1
    @JeremyMiller, no problem, glad to help! Note that it doesn't work if you put the column position integer inside any type of expression. I don't recommend using this feature, because a query's sort order can get confused if you change the order of columns in the select-list. – Bill Karwin Jun 09 '14 at 07:22

4 Answers4

5

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you Bill. Your comment makes sense to me. However, someone else suggests to trim the string and it works. Both of these 2 methods work. But they seem to be different, right? – Kenny Jun 09 '14 at 07:37
  • Yes, both work because using the cust_type_cd column inside a string function forces MySQL to replace with the string value of the enum, not the ordinal position. Both spoil the use of an index. – Bill Karwin Jun 09 '14 at 14:48
1

Use below Query. It seems there is some space before I character.

select cust_id,trim(cust_type_cd) cust_type_cd,city,state,fed_id from customer order by 2 asc

Krishna
  • 438
  • 5
  • 18
  • As a stackoverflow user always accept answers if it worked for you and try to vote instead of saying thanks. This is just to save others time. Happy coding.. – Krishna Jun 09 '14 at 07:43
  • I know. Just one more question. I am not sure the reason is numeration data type (as suggested by Bill above) or blank space before string. Both of them work. – Kenny Jun 09 '14 at 08:13
  • If it has blank space then this query should return some records else Bill is right. select cust_id,cust_type_cd,city,state,fed_id from customer where cust_type_cd like ' I%' I feel it is important to confirm otherwise it may create problem for you if it contains space. – Krishna Jun 09 '14 at 10:47
1

Using order by column numbers is strictly not recommended. It is especially not used when SELECT * is not used with this. Also it will create problems when somebody alters the table, adds/removes some columns. This link might help you http://blog.sqlauthority.com/2010/12/27/sql-server-order-by-columnname-vs-order-by-columnnumber/

paradox
  • 377
  • 3
  • 12
0

You have to give order by column name in the table Suppose if you want to sort according to cust_id you have to use 

select cust_id,cust_type_cd,city,state,fed_id from customer order by cust_id asc;
Vishnu
  • 11,614
  • 6
  • 51
  • 90