Are SQL GROUP BY fields commutative in all cases?

Question

In a simple query the order of your GROUP BY fields makes no difference (ignoring developer legibility) to the final result.

EG: SELECT COUNT(*) FROM People GROUP BY Age, Gender will produce the same results as if the GROUP BY fields were flip-flopped.

Generally speaking, under what condition(s) does this apparent commutative property of the GROUP BY fields break down?

I'm looking for a general rule here (EG: "Any expression containing sub-expressions which depend upon one of the grouped fields")

I'm having a hard time coming up with an example of where the ordering would matter - but my gut tells me it does in some situation(s).

Answer 1

I think the only time it matters is when using ROLLUP to create subtotals

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

CREATE TABLE #Prod
(
    ID INT IDENTITY(1,1),
    Cat INT,
    Size Int
)

INSERT #Prod SELECT 1,1
INSERT #Prod SELECT 1,1
INSERT #Prod SELECT 1,2
INSERT #Prod SELECT 1,3
INSERT #Prod SELECT 1,3
INSERT #Prod SELECT 1,3
INSERT #Prod SELECT 2,1
INSERT #Prod SELECT 2,2
INSERT #Prod SELECT 2,2
INSERT #Prod SELECT 2,3
INSERT #Prod SELECT 2,3
INSERT #Prod SELECT 2,3

SELECT 
COUNT(*)
FROM #Prod
GROUP BY Cat, Size WITH ROLLUP

SELECT 
COUNT(*)
FROM #Prod
GROUP BY Size , Cat WITH ROLLUP

Results from Query 1

2 1 3 6 1 2 3 6 12

(9 row(s) affected)

Results from Query 2

2 1 3 1 2 3 3 3 6 12

(10 row(s) affected)

Answer 2

I am only speculating here, but it might be possible that if someone implements CLR based aggregate functions, that the order would matter. I've implemented a aggregate function before in C# and I have this feeling that depending on what the aggregation is actually doing, that there may be a chance that the order of the group by may effect it.

I don't know enough about how aggregate CLR functions interact with the engine to really say anything more than that :/