CODEWITHSUNDEEP
sql
CDT
CDT

Reputation: 10641

This SQL 'ORDER BY' is not working properly

SELECT test_column FROM test_table ORDER BY test_column gives me this:

1
12
123
2
3

Why not:

1
2
3
12
123

How can I sort strings like numbers?

Upvotes: 17

Views: 74627

Answers (6)

Abubakar Abdullahi
Abubakar Abdullahi

Reputation: 1

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

Upvotes: 0

Ramesh Singh
Ramesh Singh

Reputation: 11

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

Upvotes: 0

vineet
vineet

Reputation: 14236

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

Upvotes: 8

Dennis Traub
Dennis Traub

Reputation: 51634

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.

Upvotes: 3

JBrooks
JBrooks

Reputation: 10013

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.

Upvotes: 24

Mat
Mat

Reputation: 206699

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

Upvotes: 2

Related Questions