Sort Order Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sort Order Problem

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2003-03-12 : 04:28:19

I am having trouble with my sorting...

declare @orderme table(
sortme varchar(100)
)

insert into @orderme
select 'test 1'
union
select 'test 2'
union
select 'test 10'
union
select 'apples 1'
union
select 'apples 2'
union
select 'apples 10'

select sortme from @orderme
order by sortme

produces

apples 1
apples 10
apples 2
test 1
test 10
test 2

it should produce
apples 1
apples 2
apples 10
test 1
test 2
test 10

"apples" and "test" could be any text string at all...

HELP!

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-12 : 04:41:34

Check BOL on topic 'sort order, described', helps you to know how a Sort Order is worked out.

___________________________________________________________________________
Success is not a destination that you ever reach. Success is the quality of your journey.

uberman
Posting Yak Master

159 Posts

Posted - 2003-03-12 : 04:46:31

I've been through BOL, messed with collations, and I have google'd my little heart out before I posted this, and if this is easy I must be missing something obvious.

Any direct pointers appreciated.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-12 : 07:12:50

Actually, there is nothing surprising about the results that you are getting. SQL is attempting to sort the whole thing as a string, and thus you will always get results like:

Apple 1
Apple 10
Apple 11
Apple 2
Apple 21...etc

simply because 1 comes before 2 in the ASCII table, hence ALL 1's will appear before the 2's. The solution might be to split this column into a string column and a number column, and then sort by the string col, number col. something like this should do it (havent tested it though):

SELECT sortme, SUBSTRING(sortme, 1, CHARINDEX(sortme, ' ', 1) - 1) AS StringCol, CAST(SUBSTRING(sortme, CHARINDEX(sortme, ' ', 1), LEN(sortme) - CHARINDEX(sortme, ' ', 1)) AS INT) AS NumberCol ORDER BY StringCol, NumberCol
FROM @orderme

OS

Edited by - mohdowais on 03/12/2003 15:51:40

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-12 : 16:06:45

It is definitely sorting it correctly. Windows also does the same thing. If you sort your files in a particular directory by name, and say you have file1.txt, file2.txt, and file10.txt, it will sort it like this:
file1.txt
file10.txt
file2.txt

Since the file names are ASCII characters, it gets sorted according to the ASCII table like mohdowais mentioned.

Tara

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-13 : 02:04:22

Alright this one works

select sortme, left(sortme, charindex(' ', col1, 0)-1) AS StringCol,
CAST(substring(sortme, charindex(' ', col1, 0), 100) AS INT) AS NumCol
from @orderme ORDER BY StringCol, NumCol

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources