CODEWITHSUNDEEP
mysqlsqlsql-order-by
Adnan
Adnan

Reputation: 26350

Order by mixed alpha-numeric column in MySQL

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?

Upvotes: 3

Views: 2556

Answers (4)

Simon Mbatia
Simon Mbatia

Reputation: 301

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

Upvotes: 0

user2207115
user2207115

Reputation: 53

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

Upvotes: 0

a'r
a'r

Reputation: 36999

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)

Upvotes: 3

BuZz
BuZz

Reputation: 17475

There is something here : http://www.carlos-roque.com/2011/04/19/sorting-characters-and-numbers-in-mysql/

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

Upvotes: 3

Related Questions