Categories
Programming

Problematic SQL Sorting

Sometimes, all you want to do is a simple ORDER BY, but it just doesn’t work. SQL has some quirks when trying to sort “text” numbers, especially when they are decimal numbers.

Consider the following Database table, myTable:


Code varchar(5) | Descr varchar(100)
------------------|--------------------
1 | Code 1
1.1 | Code 1 point 1
1.2 | Code 1 point 2
2 | Code 2
3 | Code 3
10 | Code 10
11 | Code 11
20 | Code 20

If you wanted to do a simply query to order by the Code field, one would think that a simple ORDER BY would do the trick.


SELECT
Code
,Descr
FROM
myTable
ORDER BY
Code ASC

Unfortunately, it doesn’t. This produces:


Code | Descr
------------------|--------------------
1 | Code 1
1.1 | Code 1 point 1
1.2 | Code 1 point 2
10 | Code 10
11 | Code 11
2 | Code 2
20 | Code 20
3 | Code 3

In order for the DBMS to interpret the Code field as a Number, not as text, we need to CAST it to a numeric datatype, in this case i will use float.


SELECT
cast(Code as float) AS 'Code'
,Descr
FROM
myTable
ORDER BY
Code ASC

Yup, this doesn’t work either. Suddenly you may find yourself with 1.999999999999 instead of your code 2. The rounding mechanism is not perfect. So, we need to take this a step further, and once again re-cast our float as a varchar. This will fix the rounding problem.


SELECT
cast(cast(Code as float)as varchar) AS 'Code'
,Descr
FROM
myTable
ORDER BY
Code ASC

The above will produce your results table nice and neat and perfectly sorted.


Code | Descr
------------------|--------------------
1 | Code 1
1.1 | Code 1 point 1
1.2 | Code 1 point 2
2 | Code 2
3 | Code 3
10 | Code 10
11 | Code 11
20 | Code 20

Knowing how to work around the tweaks of any system will save you a lot of time and hair pulling. Fortunately, there are built in functions that make it easy on us developers, but sometimes finding them can be a little tricky.

This is the first of my SQL articles to come, so check back often for new articles and updates.

This code was tested for MSSQL Server 2000. Rounding quirks may react differently in your DBMS.

Like this article? Please Digg it

3 replies on “Problematic SQL Sorting”

Hi Liana,

I never worked with “numbers” such as that, as techncially, its not a Real number. I can see the use though, perhaps in sorting “Sections” of a legal document, for example.

When I have more time, I will play with this some and post what I come up with.

Code does not work among this set of values :
0.1
100
115.2
133.3
145
145
150
23.5
299
45
47

as can see, 23.5 should come before the hundreds but the code sorts based on the first numeric value in the number, as in 0 1 2 3 4

Leave a Reply

Your email address will not be published. Required fields are marked *