Order by mixed alpha-numeric column in MySQL

Question

I have the following values in my rows (they should be ordered like this);

**CLASS_CODE**
6A
6B
6C
10A
10B
10C

Well if I do a simple ORDER BY CLASS_CODE, I will get first 10x then 6x values.

So I use ORDER BY (CLASS_CODE+0), this orders them correctly so that 6x comes first, but it does not order them accordingly to chars as well.

What would be the correct way to order by, so I can get the correct order as shown above?

because MySQL will do only a string comparision 1 is before 6. What you want is a natural sort (like in PHP i.e. for arrays). I don't know if that is possible in MySQL — rabudde, Nov 25 '11 at 09:00

score 3 · Answer 1 · edited May 23 '17 at 12:06

3

I can't test it right now, but I believe something like that can be a nice hack :

SELECT CLASS_CODE as hack ... ORDER BY (CLASS_CODE+0)ASC, hack ASC

Maybe try to turn it around.

If that's a fail, here is some discussion about sorting in a natural fashion : Natural Sort in MySQL

edited May 23 '17 at 12:06

Community

answered Nov 25 '11 at 09:05

BuZz

No need to alias `CLASS_CODE`, I'm sure `ORDER BY (CLASS_CODE+0)ASC, CLASS_CODE ASC` would do just as well. – Andriy M Nov 25 '11 at 09:11
I see, thought it would not be considered twice. You must be right. – BuZz Nov 25 '11 at 09:12
Your basic idea is still correct. I can agree both with your answer and with @a'r's. – Andriy M Nov 25 '11 at 09:19

score 3 · Accepted Answer · answered Nov 25 '11 at 09:07

3

As long as you only use a single letter at the end of the field values, then you can use the following:

ORDER BY (class_code+0), right(class_code, 1)

answered Nov 25 '11 at 09:07

a'r

I only have one letter at the end. Thank you for your solution. – Adnan Nov 25 '11 at 09:13
The example by the OP indeed leads to assume that there's always one character after a number, but in general that may not be true. And even if it is, you still don't have to extract the rightmost character. Once you've got the order by `(class_code+0)`, the subgroups can be ordered simply by `class_code`. – Andriy M Nov 25 '11 at 09:13

score 0 · Answer 3 · answered Aug 09 '13 at 05:47

Sorry for the late reply. But it may help someone.

You have to implement Natural Sorting. To sort it using PHP, you can use natsort() function.

You can achieve natural sorting through MySQL also which is simple enough. For ex:

ORDER BY LENGTH(class_code), class_code

score 0 · Answer 4 · answered Feb 27 '15 at 10:12

0

I have had this scenario and I use the CAST function. In this case ORDER BY CAST(class_code AS DECIMAL)

answered Feb 27 '15 at 10:12

Simon Mbatia

4 Answers4