The GROUPING_ID TSQL Function

The GROUPING_ID function computes the level of grouping in a resultset. It can be used in the SELECT, HAVING or ORDER BY clauses when used along with GROUP BY. The expression used in this function must match what has been used in the GROUP BY clause.

This function can be usefully deployed to order a resultset according to it’s grouping hierarchy. For example we may wish to order aggregated sales results according to period.

WITH MyCTE
(
	Division,
	[Quarter],
	Value
)
AS
(
	SELECT 'Sales 1', 1, 0.12
	UNION ALL
	SELECT 'Sales 1', 1, 0.34
	UNION ALL
	SELECT 'Sales 1', 2, 0.45
	UNION ALL
	SELECT 'Sales 2', 3, 0.77
	UNION ALL
	SELECT 'Sales 2', 1, 0.55
	UNION ALL
	SELECT 'Sales 1', 3, 0.78
	UNION ALL
	SELECT 'Sales 2', 2, 0.45
	UNION ALL
	SELECT 'Sales 2', 4, 0.67
	UNION ALL
	SELECT 'Sales 1', 4, 0.77
)
SELECT Division,
	   [Quarter],
	   SUM(Value) AS SummedValue,
	   GROUPING(Division) AS IsDivisionGroup,
	   GROUPING([Quarter]) AS IsQuarterGroup,
	   GROUPING_ID(Division, [Quarter])
FROM MyCTE
GROUP BY Division,
		 [Quarter]
WITH ROLLUP
ORDER BY GROUPING_ID(Division, [Quarter]);

Note how the resultset is ordered showing increasingly aggregated rows at the end.