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

This entry was posted in Programming. Bookmark the permalink.

3 Responses to Problematic SQL Sorting

  1. liana says:

    how about many point, i mean for example “1.1.10″….how to sort it,do you have any idea to help me

  2. 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.

  3. Eric says:

    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 *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>