How would I order a MySQL query by letter and number, sorting 100 as higer than 010?

Question

We have product codes that go as follows:

AGE100 (stands for AGE 1.00 ounce)
AGE050 (stands for AGE 0.50 ounce)
AGE025 (stands for AGE 0.25 ounce)
AGE010 (stands for AGE 0.10 ounce)

So on and so forth. I would like the database to return them alphabet first, then by the number code at the end, so KR100 would be lower than AGE050.

My current query,

 SELECT * FROM  'prices' ORDER BY  'code' ASC LIMIT 0 , 30

Returns:

AGE010
AGE025
AGE050
AGE100
KR100

How would I make it sort the numbers in the code properly?

Remove all non-numeric characters, convert to a number then do a standard binary sort... — Ben, May 01 '13 at 20:24
Could you add the complete sort order that would be correct? — Joachim Isaksson, May 01 '13 at 20:26
it is two sorts - ASC on the char portion, and DESC on the numeric portion. first chore is to split them - then the ordering will be easy — Randy, May 01 '13 at 20:27

score 0 · Answer 1 · answered May 01 '13 at 20:35

To sort descending by the number portion:

SELECT * FROM  'prices' ORDER BY SUBSTRING('code', -3) DESC

and the first 2 chars

SELECT * FROM  'prices' ORDER BY SUBSTRING('code', 1,2) ASC

so this should give you a something close:

SELECT * FROM  'prices' ORDER BY SUBSTRING('code', 1,2) ASC, SUBSTRING('code', -3) DESC

score 0 · Answer 2 · answered May 01 '13 at 20:35

To place items in a specific order you could use an sortby column in the database that you can change to finely adjust the order that the rows should be pull out in.

Example

SELECT * FROM  'prices' ORDER BY  'code' ASC LIMIT 0 , 30

would become

SELECT * FROM  'prices' ORDER BY  'sortby' ASC LIMIT 0 , 30

This field would be an INT and would make it much easier to get the desired order.

score -1 · Answer 3 · answered May 01 '13 at 20:32

-1

Since natsort() doesn't work with multidimensional arrays, you can try:

uksort($array, 'strnatcasecmp');

answered May 01 '13 at 20:32

eidsonator

1,319
2
11
25

How would I order a MySQL query by letter and number, sorting 100 as higer than 010?

3 Answers3