SQL for ordering by number - 1,2,3,4 etc instead of 1,10,11,12

sql sql-order-by
Konnor262 picture Konnor262 · May 13, 2013 · Viewed 174.3k times · Source

I’m attempting to order by a number column in my database which has values 1-999

When I use 

ORDER_BY registration_no ASC

I get….

1
101
102
103
104
105
106
107
108
109
11
110
Etc…

So it appears to be ordering by the first digit as oppose to the number.

Does anyone know what SQL to use if I want to order this by value? So 1,2,3,4,5,6 etc

Answer

Gordon Linoff picture Gordon Linoff · May 13, 2013

One way to order by positive integers, when they are stored as varchar, is to order by the length first and then the value:

order by len(registration_no), registration_no

This is particularly useful when the column might contain non-numeric values.

Note: in some databases, the function to get the length of a string might be called length() instead of len().

Related questions

SQL multiple column ordering

I am trying to sort by multiple columns in SQL, and in different directions. column1 would be sorted descending, and column2 ascending. How can I do this?

Read More
How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
How to order by with union in SQL?

Is it possible to order when the data is come from many select and union it together? Such as Select id,name,age From Student Where age < 15 Union Select id,name,age From Student Where Name like "%a%" How …

Read More