Michael Meierruth (3/10/2011)
Jeff,Neat stuff on hierarchies!
One thing that's confusing me is the use of the back slash and how it orders.
Thus in the script below, why do I get two different ordering results when I order on c1 as opposed to c2?
select *
from
(
select 'AA' c1,CAST('AA' as binary(4)) c2
union all
select 'A\',CAST('A\' as binary(4))
) t
order by c1
It's because of the collation settings for the server. For c1, it's purely an "alpha" sort based on collation. For c2, collation does not come into effect because it's binary. Since the backslash (ASCII character #92) has a larger ASCII value than the letter "A" (ASCII character #65), it sorts differently for the binary version. It would also sort c1 differently if you used one of the binary collations.
--Jeff Moden
Change is inevitable... Change for the better is not.