CODEWITHSUNDEEP
mysqlsql-order-by
shin
shin

Reputation: 32721

How to order 1,2,3 instead of 1, 10, 11, 12 in MySQL when the column to sort by is a string?

The following query gets results ordered like: 1, 10, 11, 12, 2, 3

I want my results sorted like 1, 2, 3, [...], 9, 10, 11...

SELECT P.*, C.Name AS CatName 
FROM products AS P 
LEFT JOIN categories C ON C.id = P.category_id'

Upvotes: 49

Views: 56716

Answers (6)

LukasKroess
LukasKroess

Reputation: 357

If you are certain that your numbers never exceed a certain length, you could LPAD the column with a bunch of zeros before sorting, like so:

SELECT
    P.*, 
    C.Name AS CatName
FROM
    products AS P
LEFT JOIN
    categories C
ON
    C.id = P.category_id
ORDER BY 
    LPAD(P.id, 8, '0')

That way, it would still sort alphanumerically, but the order would match their numeric value.

NOTE: I do realise this is not the prettiest, nor the fastest, nor the safest solution (I probably wouldn't even use this one myself), but I think this approach should at least be mentioned here for anyone stumbling upon this question.

Upvotes: 2

halfdan
halfdan

Reputation: 34234

You can do an explicit cast by doing:

ORDER BY CAST(category_id AS UNSIGNED INTEGER)

But you should reconsider you database layout as a field containing only numeric values should also be of an numeric type..

Upvotes: 14

CHanaka
CHanaka

Reputation: 502

Order by only works for numeric values(int, decimal, ...), not for varchar, char, etc.

Your category_id should be numerical, otherwise you need to cast the data to it's numerical value.

Upvotes: 0

Andreas Bergström
Andreas Bergström

Reputation: 14630

As previously mentioned MySQL doesn't support alphanumeric sorting. One common trick to solve this is to first order by length:

ORDER BY LENGTH(column_name), column_name

As long as the non-numeric part of the value is the same length, this will sort 1 before 10, 10 before 100, etc.

Upvotes: 24

Jakub
Jakub

Reputation: 20475

Make sure that the column that holds 1,2,3,4 is INT type, if it is TEXT, you will not get numerical order, but what you describe 1, 10, 11, 2, 22, 23, 31, etc;

And like others mentioned, use ORDER BY

Upvotes: 6

Scott Saunders
Scott Saunders

Reputation: 30414

First, add an order by clause at the end:

ORDER BY category_id

If category_id is a string, then you must treat it like an integer. There are a few ways to do this. I usually add a zero. You can also cast it.

ORDER BY category_id + 0

Upvotes: 80

Related Questions