How to order 1,2,3 not 1, 10, 11, 12 in mySQL

Question

The following code outputs in order of 1, 10, 11, 12 of id.

I want to make it 1,2,3,4...

Could anyone tell me what I should do please.

$Q = $this->db->query('SELECT P.*, C.Name AS CatName FROM products AS P LEFT JOIN categories C ON C.id = P.category_id');

Thanks in advance.

I'm assuming you've tried the (obvious) addition of `ORDER BY P.id` to the query? — jamuraa, Dec 02 '09 at 14:04

score 79 · Answer 1 · answered Dec 02 '09 at 14:05

79

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

answered Dec 02 '09 at 14:05

Scott Saunders

29,840
14
57
64

"category_id" should obviously be whichever field you want to sort by. – Scott Saunders Dec 02 '09 at 14:06

score 20 · Answer 2 · answered Mar 24 '17 at 06:19

20

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.

answered Mar 24 '17 at 06:19

Andreas Bergström

13,891
5
59
53

This solved my issue with ORDER BY 'role' column: 'telemarketer1', 'telemarketer10', 'telemarketer2' – 6opko Feb 26 '19 at 14:26

score 13 · Answer 3 · edited Dec 04 '19 at 16:09

13

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..

Best wishes, Fabian

edited Dec 04 '19 at 16:09

Pathik Vejani

4,263
8
57
98

answered Dec 02 '09 at 14:28

halfdan

33,545
8
78
87

this also helped me with SQLite, where - for some reason - a REAL column didn't sort numerically. – amenthes Jul 02 '18 at 14:48
Much appreciate this answer, I never had the issue before since I use INT fields and such BUT I was using a MYSQL function for getting months from a date field and I realized it was not sorting correctly – carnini May 16 '22 at 20:40

score 6 · Answer 4 · answered Dec 02 '09 at 14:06

6

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

answered Dec 02 '09 at 14:06

Jakub

20,418
8
65
92

score 0 · Answer 5 · answered May 16 '16 at 06:52

0

Order by only working for numerical values(int), not work for varchar, char

Your category_id should numerical, otherwise you need to cast values to numerical.

answered May 16 '16 at 06:52

CHanaka

472
5
10

score -2 · Answer 6 · answered Dec 02 '09 at 14:05

-2

Well, you're not setting any ORDER BY clause.

answered Dec 02 '09 at 14:05

jensgram

31,109
6
81
98

How to order 1,2,3 not 1, 10, 11, 12 in mySQL

6 Answers6

Linked