Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strange 'order by' - can anyone explain ? Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 2:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:48 AM
Points: 11, Visits: 49
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.
Post #1489064
Posted Wednesday, August 28, 2013 2:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
I didn't get that. What is your server collation set to?

John
Post #1489066
Posted Wednesday, August 28, 2013 2:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1489068
Posted Wednesday, August 28, 2013 4:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:48 AM
Points: 11, Visits: 49

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
Post #1489104
Posted Wednesday, August 28, 2013 4:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1489105
Posted Wednesday, August 28, 2013 4:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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








____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1489110
Posted Wednesday, August 28, 2013 4:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Post #1489111
Posted Wednesday, August 28, 2013 3:56 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 756, Visits: 631
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
Post #1489445
Posted Thursday, August 29, 2013 1:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:48 AM
Points: 11, Visits: 49
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
Post #1489560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse