SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange 'order by' - can anyone explain ?


Strange 'order by' - can anyone explain ?

Author
Message
Thorkil Johansen
Thorkil Johansen
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 61
Hi - I simply don't understand this sorting... Do you know why ?


IF OBJECT_ID('tempdb.dbo.#abthja_1') IS NOT NULL
BEGIN
DROP TABLE #abthja_1
END
CREATE TABLE #abthja_1 (
postkasse varchar (15) not null
,folder varchar(15) not null
,modtaget int
,lost int)

IF OBJECT_ID('tempdb.dbo.#abthja_2') IS NOT NULL
BEGIN
DROP TABLE #abthja_2
END
CREATE TABLE #abthja_2 (
postkasse varchar (15) not null
,folder varchar(15) not null
,modtaget int
,lost int)

insert into #abthja_1
select 'Salg','AAA', 1, NULL
union all select 'Salg','CCC', 2, NULL
union all select 'Salg','BBB', 2, NULL
select * from #abthja_1

insert into #abthja_2
select 'Salg','AAA/Lo', NULL, 4
union all select 'Salg','CCC', NULL, 5
union all select 'Salg','BBB/Lo', NULL, 6
select * from #abthja_2

select postkasse, folder, sum(modtaget) as modtaget, sum(lost) as lost
from (
select *
from #abthja_1
union all
Select *
from #abthja_2) data
group by postkasse, folder
order by postkasse, folder


Result:

postkasse folder modtaget lost
Salg BBB 2 NULL
Salg BBB/Lo NULL 6
Salg CCC 2 5
Salg AAA 1 NULL
Salg AAA/Lo NULL 4

If you change BBB to 2BB,
AAA to 1AA
CCC to 3CC
then you got what you would expect ?

Regards from Copenhagen.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35317 Visits: 16683
I didn't get that. What is your server collation set to?

John
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8758 Visits: 3718
What is the collation you use for your instance?
In what order do you want your results?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Thorkil Johansen
Thorkil Johansen
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 61
Hi - thanx for taking your time...

Collation is Danish_Norwegian_CS_AS, but does it matter here ?

I mean... how would you expect AAA, BBB, CCC to be sorted ?

I don't think there is a collation that will place CCC in the middle ? :-)

/T
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8758 Visits: 3718
If I apply the "Danish_Norwegian_CS_AS" collation to the query it does indeed sort BB-CC-AA. So yes, the collation does matter.
I don't know why, but perhaps that's because I'm not Danish ;-).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6569 Visits: 25602
Is there some significance in 'AA' in Danish/Norwegian? It seems to sort after 'Z'


SELECT X
FROM (VALUES ('A'),('AA'),('B'),('BB'),('C'),('CC'),('Z')) T(X)
ORDER BY X COLLATE Latin1_General_CI_AS

SELECT X
FROM (VALUES ('A'),('AA'),('B'),('BB'),('C'),('CC'),('Z')) T(X)
ORDER BY X COLLATE Danish_Norwegian_CS_AS



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35317 Visits: 16683
Mark-101232 (8/28/2013)
Is there some significance in 'AA' in Danish/Norwegian? It seems to sort after 'Z'

Yes, I think you've nailed it. Take a look at the very bottom line of this page.

John
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5364 Visits: 875
Thorkil, which of these lists do you think is correct:

Aalborg, Aarhus, Amager, Bornholm, Ringsted, Ærö, Øresund

Amager, Bornholm, Ringsted, Ærö, Øresund, Aalborg, Aarhus

There you have it.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Thorkil Johansen
Thorkil Johansen
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 61
Hey all

You are so right... What a fool I am...

This double-a... something from the past... But still in effect..

Thaxs again to this nice forum...

/T
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search