Strange sorting

[Peter Herijgers]

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

[Chris.Cheney]

Post by Peter Herijgers
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)

SomeField or ItemNo? Be consistent to help us to help you.

SELECT ItemNo FROM MyTable ORDER BY CAST(ItemNo AS int)

would have sufficed.

Post by Peter Herijgers
Any ideas why the first SQL Query is not working?

But it IS working. Since the data are character strings, the comparisons
are on character strings, such that "1" < "2" < "2200" < "8" < "880"

To order by integer value: you can convert to integers (as you have done)
or you could store the data with an appropriate number of leading zeros,
e.g.

"0001", "0880", "2200"

Post by Peter Herijgers
Is this a known problem of MSDE?

Computers do what you tell them to do, which is not necessarily what you
want them to do!

Post by Peter Herijgers
Later this year I want to upgrade to SQL 2008.
Thanks in advance.
Peter

[William Vaughn (MVP)]

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
[phone] (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Post by Peter Herijgers
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