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

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

sqlsql-order-by


Solution 1:[1]

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

Solution 2:[2]

ORDER_BY cast(registration_no as unsigned) ASC

explicitly converts the value to a number. Another possibility to achieve the same would be

ORDER_BY registration_no + 0 ASC

which will force an implicit conversation.

Actually you should check the table definition and change it. You can change the data type to int like this

ALTER TABLE your_table MODIFY COLUMN registration_no int;

That way indexes can be used properly and the order by won't slow down the query.

Solution 3:[3]

If you are using SQL Server:

ORDER_BY cast(registration_no as int) ASC

Solution 4:[4]

ORDER_BY cast(registration_no as unsigned) ASC

gives the desired result with warnings.

Hence, better to go for 

ORDER_BY registration_no + 0 ASC

for a clean result without any SQL warnings.

Solution 5:[5]

I assume your column type is STRING (CHAR, VARCHAR, etc) and sorting procedure is sorting it as a string. What you need to do is to convert value into numeric value. How to do it will depend on SQL system you use.

Solution 6:[6]

Sometimes you just don't have a choice about having to store numbers mixed with text. In one of our applications, the web site host we use for our e-commerce site makes filters dynamically out of lists. There is no option to sort by any field but the displayed text. When we wanted filters built off a list that said things like 2" to 8" 9" to 12" 13" to 15" etc, we needed it to sort 2-9-13, not 13-2-9 as it will when reading the numeric values. So I used the SQL Server Replicate function along with the length of the longest number to pad any shorter numbers with a leading space. Now 20 is sorted after 3, and so on.

I was working with a view that gave me the minimum and maximum lengths, widths, etc for the item type and class, and here is an example of how I did the text. (LBnLow and LBnHigh are the Low and High end of the 5 length brackets.)

REPLICATE(' ', LEN(LB5Low) - LEN(LB1High)) + CONVERT(NVARCHAR(4), LB1High) + '" and Under' AS L1Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB2Low)) + CONVERT(NVARCHAR(4), LB2Low) + '" to ' + CONVERT(NVARCHAR(4), LB2High) + '"' AS L2Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB3Low)) + CONVERT(NVARCHAR(4), LB3Low) + '" to ' + CONVERT(NVARCHAR(4), LB3High) + '"' AS L3Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB4Low)) + CONVERT(NVARCHAR(4), LB4Low) + '" to ' + CONVERT(NVARCHAR(4), LB4High) + '"' AS L4Text,
CONVERT(NVARCHAR(4), LB5Low) + '" and Over' AS L5Text

Solution 7:[7]

This problem is just because you have declared the column in CHAR, VARCHAR or TEXT datatype. Just change the datatype to INT, BIGINT etc. This is will solved the problem of your custom ordering.

Solution 8:[8]

I prefer doing a "PAD" to the data. MySql calls it LPAD, but you can work your way around to doing the same thing in SQL Server. 

ORDER BY  REPLACE(STR(ColName, 3), SPACE(1), '0')

This formula will provide leading zeroes based on the Column's length of 3. This functionality is very useful in other situations outside of ORDER BY, so that is why I wanted to provide this option.

Results: 1 becomes 001, and 10 becomes 010, while 100 remains the same.

Related Questions