ORDER BY not working correctly?

Question

SELECT test_column FROM test_table ORDER BY test_column gives me the following:

 1 12 123 2 3 

Why not:

 1 2 3 12 123 

@JosephPla You have a point, thanks. How to sort strings like numbers?

Answer 1

Try

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

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

Answer 2

This work is for me: -

 ORDER BY cast(test_column as SIGNED) 

Answer 3

Sorting works. This is a lexicographic view (in alphabetical order). It looks like there is text in this column (char, varchar, ...), so the ordering you get is textual, not numeric.

If you want to sort numerically, use a numeric column type (e.g. int). (Or enter the column accordingly.)

Answer 4

Check if the column type is varchar or something similar. It appears to be ordered by a string value, not a numerical value. If the column contains only digits, it must be of type int.