SkeyMedia

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