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

Question

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

User · Answer

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

User · Answer

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

User · Answer

If you are using SQL Server   ORDER BY cast registration no as int  ASC

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-order-by