This SQL 'ORDER BY' is not working properly

Question

SELECT test_column FROM test_table ORDER BY test_column gives me this:

Why not:

How can I sort strings like numbers?

Do something like SELECT test_column FROM test_table ORDER BY convert(int,test_column) — Sumit Kapadia, Jul 20 '13 at 13:17
If type of `test_table` is string based, The `ORDER BY` sorts them based on first character ASCII code — frogatto, Jul 20 '13 at 13:18

score 23 · Accepted Answer · answered Jul 20 '13 at 13:18

23

Try

SELECT test_column 
FROM test_table 
ORDER BY cast(test_column as int)

But you should look into changing the column types to the correct ones.

answered Jul 20 '13 at 13:18

JBrooks

9,901
2
28
32

2

Worth noting that `CAST` will not use any index. – Kermit Jul 20 '13 at 13:31

vineet · Answer 2 · 2021-07-18T15:51:00.510

8

This worked for me:

ORDER BY cast(test_column as SIGNED)

here, cast function convert value from string to integer(SIGNED) then it applied ORDER BY. https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

edited Jul 18 '21 at 15:51

answered Sep 29 '15 at 13:04

vineet

13,832
10
56
76

An explanation would be in order. Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/32844785/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Jul 18 '21 at 12:38
added cast function brief explanation. – vineet Jul 18 '21 at 15:51

score 3 · Answer 3 · edited Jul 18 '21 at 12:37

3

Check if the type of the column is varchar or something similar. It looks like it is being ordered by string value, not by numeric value. If the column only contains numbers it should better be of type int.

edited Jul 18 '21 at 12:37

Peter Mortensen

30,738
21
105
131

answered Jul 20 '13 at 13:19

Dennis Traub

50,557
7
93
108

score 2 · Answer 4 · answered Jul 20 '13 at 13:18

The sort is working. It's a lexicographic sort (alphabetical). It appears that that column has a text (char, varchar, ...) type, so the ordering you'll get is textual and not numeric.

If you want a numerical sort, use a numeric column type (e.g. int). (Or cast the column appropriately.)

score 0 · Answer 5 · edited Oct 07 '22 at 11:09

0

I think , we need to do the cast as part of the select statement , because if we use distinct then casting in the order by statement does not work:

  SELECT cast(test_column as int) as test_column 
    FROM test_table
ORDER BY test_column

and

  SELECT DISTINCT cast(test_column as int) as test_column 
    FROM test_table 
ORDER BY test_column

edited Oct 07 '22 at 11:09

buddemat

4,552
14
29
49

answered Oct 01 '22 at 08:24

Ramesh Singh

11
1

you are repeating what's in the accepted answer from 10 years ago.....you are contributing nothing of value. – Mitch Wheat Oct 01 '22 at 08:31
My concern was only with distinct keywords.It is my mistake to do comment instead of reply on accepted answer. – Ramesh Singh Oct 03 '22 at 01:01

score 0 · Answer 6 · edited Apr 16 '23 at 07:53

0

Change the field type in the database table to int, because it's treated as string that is why

edited Apr 16 '23 at 07:53

J. Chris Compton

538
1
6
25

answered Apr 09 '23 at 12:34

Abubakar Abdullahi

1

This SQL 'ORDER BY' is not working properly

6 Answers6

Linked

Related