Strange 'order by' - can anyone explain ?
-
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:
postkassefoldermodtagetlost
SalgBBB2NULL
SalgBBB/LoNULL6
SalgCCC25
SalgAAA1NULL
SalgAAA/LoNULL4
If you change BBB to 2BB,
AAA to 1AA
CCC to 3CC
then you got what you would expect ?
Regards from Copenhagen.
-
I didn't get that. What is your server collation set to?
John
-
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’! ** -
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
-
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’! ** -
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
-
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
-
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply