weird ordering ' ' comes before ''

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 2005 Forums

Transact-SQL (2005)

weird ordering ' ' comes before ''

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-04-23 : 08:07:38

can someone plese explain this to me:


select *
from 
	(
		select ''  A, '1' B union all
		select ' ' A, '2'   union all
		select '-' A, '3'   union all
		select 'a' A, '4' 
	) x 
order by A

/*
-- result of the query on my servers
A   B
    2
    1
-   3
a   4
*/

by what logic does the ' ' come before ''?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-23 : 08:15:47

It looks like those last two rows causes an issue, possible bug?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-04-23 : 08:16:50

no, the first 2 rows are weird. empty string would by my logic come before space.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-23 : 08:18:17

quote:
Originally posted by spirit1

no, the first 2 rows are weird. empty string would by my logic come before space.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Yes, empty string should come first. When I leave only the first two rows, it works fine. As soon as there is a third row, it sorts incorrectly. Possible bug?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-23 : 08:20:02

Bah nevermind.... need coffee.

An infinite universe is the ultimate cartesian product.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-23 : 08:21:01

quote:
Originally posted by cat_jesus

I'm guessing it's because the ascii value is null

select ascii('')
select ascii(' ')

An infinite universe is the ultimate cartesian product.

Isn't null suppose to come first though?

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-23 : 08:22:34

yeah, I'm up too early, thought I'd catch it before I infected anyone else with my confusion.

An infinite universe is the ultimate cartesian product.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-04-23 : 08:23:33

yes null is supposed to come first.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

pootle_flump

1064 Posts

Posted - 2009-04-23 : 08:23:33

I can't. But some interesting stuff if you play around.

select *
from 
	(
		SELECT	daChar	= CHAR(number), number
		FROM    dbo.numbers	
		WHERE	number BETWEEN 0 AND 255
		UNION ALL
		SELECT	'', 1000
	) x 
order by daChar

select *
from 
	(
		SELECT	daChar	= CHAR(number) COLLATE latin1_general_BIN , number
		FROM    dbo.numbers	
		WHERE	number BETWEEN 0 AND 255
		UNION ALL
		SELECT	'', 1000
	) x 
order by daChar

(my default is latin1_general_CI_AS)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-04-23 : 08:25:28

SQL Server ignores trailing spaces in equality and ordering tests in all collations, so it's arbitrary which way round they appear.


SELECT '|' + A.spaces + '|', '|' + A.empty + '|',
  CASE WHEN A.spaces > A.empty THEN 1 ELSE 0 END AS bigger,
  CASE WHEN A.spaces < A.empty THEN 1 ELSE 0 END AS smaller,
  CASE WHEN A.spaces = A.empty THEN 1 ELSE 0 END AS equal
FROM ( SELECT ' ' AS spaces, '' AS empty ) AS A

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-04-23 : 08:27:41

fribble to the rescue

now that you mentioned this i remember you told us that one before.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 08:28:16

[code]select a,
b,
ascii(a) AS [ascii]
from (
select '' as A , 1 as B
union all select ' ', 2
union all select '-', 3
union all select 'a', 4
) AS x
order by A,
NEWID()[/code]

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-04-23 : 08:31:46

thanx to all!

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 08:32:08

My bot needs some repairing!

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources