Weird sorting...? - 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 2008 Forums

Transact-SQL (2008)

Weird sorting...?

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-04-13 : 04:25:52

Is this a little bit weird or is it just me...? The -1 ends up in the middle (yes I am aware that if I use different data types it will work differently) ->

declare @table table (nr varchar(4))
insert into @table values ('-1'), ('0998'), ('0999'), ('1000'), ('1001')
select * from @table order by nr

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-04-13 : 04:38:10

Its not happening at my end.

Vaibhav T

If I cant go back, I want to go fast...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-04-13 : 04:46:38

So what does it look like on your end? This is what I get:

nr
----
0998
0999
-1
1000
1001

(5 row(s) affected)

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 05:07:31

this is weird .. could it be collation ?

this is what i got

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-04-13 : 07:24:27

You're right, it's collation. I still think it's weird that my default collation sorts this way though...this is in fact the first time I've encountered a difference in sorting between Latin_General and Danish_Norwegian that doesn't have to do with the scandinavian characters (æ, ø, å) ->

select * from @table order by nr collate SQL_Latin1_General_CP1_CI_AS
select * from @table order by nr collate Danish_Norwegian_CI_AS

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-04-13 : 07:38:47

According the following article about collation in SQL 2000 Danish_Norwegian and Latin1_General has the same code page (1252). Shouldn't sorting be the same then? Confused...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-04-13 : 07:39:09

http://msdn.microsoft.com/en-us/library/aa226047(v=sql.80).aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources