Strange sorting

Hi All,

Currently I am using MSDE.
If I want to order some items the output makes no sense to me.

I have a table with 3 records.
Each record has a column (varchar data type), which holds 1, 880 and 2200.

Using SELECT SomeField FROM MyTable ORDER BY SomeField
The records are sorted like this 1, 2200, 880.

I can overcome this problem by using this
SELECT CAST(ItemNo AS int) FROM MyTable ORDER BY CAST(ItemNo AS int)

Any ideas why the first SQL Query is not working?

Is this a known problem of MSDE?
Later this year I want to upgrade to SQL 2008.

Thanks in advance.

Peter

This is 100% understandable. How would you sort the following list? A, F, C,
B
Since you recognize these as letters you (and SQL Server) know how to sort
them. However if you ask SQL Server to sort character fields that happen to
contain numbers it does the same thing so 2200 comes after 1 and 8 comes
after 2200.
If the column is supposed to store (only) numbers, cast it as a numeric
type. This will help SQL Server sort it as a number. Otherwise you have to
cast (which will hurt performance).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



"Peter Herijgers" wrote in message

> Hi All,
>
> Currently I am using MSDE.
> If I want to order some items the output makes no sense to me.
>
> I have a table with 3 records.
> Each record has a column (varchar data type), which holds 1, 880 and 2200.
>
> Using SELECT SomeField FROM MyTable ORDER BY SomeField The records are
> sorted like this 1, 2200, 880.
>
> I can overcome this problem by using this SELECT CAST(ItemNo AS int) FROM
> MyTable ORDER BY CAST(ItemNo AS int)
>
> Any ideas why the first SQL Query is not working?
>
> Is this a known problem of MSDE?
> Later this year I want to upgrade to SQL 2008.
>
> Thanks in advance.
>
> Peter