SQL for ordering by number - 1,2,3,4 etc instead of 1,10,11,12
SqlSql Order-BySql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
If you are using SQL Server:
ORDER_BY cast(registration_no as int) ASC
Solution 4 - Sql
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 - Sql
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 - Sql
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.
Solution 7 - Sql
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 8 - Sql
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.