Solutions to T-SQL Challenge - Hierarchical Aggregates and Sorting

Last week I provided a moderate level T-SQL challenge dealing with
hierarchical aggregates and sorting. If you don’t remember the details, you
can find the challengehere.
I got correct solutions from: Marcello Poletti, Giorgio Rancati, Hugo
Kornelis, Alejandro Mesa, Rob Farley, Maciej Pilecki, Takis Katsogiannos,
Dan White, and Issiran.
Regarding the result set itself (regardless of ordering), I got two solutions; a
long, and a short one.
The long one has a separate GROUP BY query for each required level of
aggregates, and a UNION ALL operator unifies the sets:

SELECT   YEAR(OrderDate)  AS OrderYear,  MONTH(OrderDate) AS OrderMonth,  DAY(OrderDate)   AS OrderDay,  OrderID, FreightFROM Northwind.dbo.OrdersUNION ALLSELECT YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), NULL, SUM(Freight)FROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)UNION ALLSELECT YEAR(OrderDate), MONTH(OrderDate), NULL, NULL, SUM(Freight)FROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate), MONTH(OrderDate)UNION ALLSELECT YEAR(OrderDate), NULL, NULL, NULL, SUM(Freight)FROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate)UNION ALLSELECT NULL, NULL, NULL, NULL, SUM(Freight)FROM Northwind.dbo.Orders

The short one simply utilizes the ROLLUP option which produces the
logical equivalent of the above. But the ROLLUP option does this with
much less code and also with better performance since the base data
doesn’t need to be scanned once per each level of aggregates:

SELECT   YEAR(OrderDate)  AS OrderYear,  MONTH(OrderDate) AS OrderMonth,  DAY(OrderDate)   AS OrderDay,  OrderID,  SUM(Freight)     AS FreightFROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderIDWITH ROLLUP

Producing the right result set was the simple part of the problem. Ordering
was the trickier part. I’ll describe four ways to achieve ordering.

Solution 1: use CASE expressions in the ORDER BY clause to ensure
NULLs sort last:

SELECT   YEAR(OrderDate)  AS OrderYear,  MONTH(OrderDate) AS OrderMonth,  DAY(OrderDate)   AS OrderDay,  OrderID,  SUM(Freight)     AS FreightFROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderIDWITH ROLLUPORDER BY  CASE WHEN YEAR(OrderDate)  IS NULL THEN 1 ELSE 0 END, OrderYear,  CASE WHEN MONTH(OrderDate) IS NULL THEN 1 ELSE 0 END, OrderMonth,  CASE WHEN DAY(OrderDate)   IS NULL THEN 1 ELSE 0 END, OrderDay,  CASE WHEN OrderID          IS NULL THEN 1 ELSE 0 END, OrderID;

Note that in order to apply this logic to the code with the UNION ALL set
operations, you will need to use a table expression like so:

SELECT *FROM (  SELECT     YEAR(OrderDate)  AS OrderYear,    MONTH(OrderDate) AS OrderMonth,    DAY(OrderDate)   AS OrderDay,    OrderID, Freight  FROM Northwind.dbo.Orders  UNION ALL  SELECT YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), NULL, SUM(Freight)  FROM Northwind.dbo.Orders  GROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)  UNION ALL  SELECT YEAR(OrderDate), MONTH(OrderDate), NULL, NULL, SUM(Freight)  FROM Northwind.dbo.Orders  GROUP BY YEAR(OrderDate), MONTH(OrderDate)  UNION ALL  SELECT YEAR(OrderDate), NULL, NULL, NULL, SUM(Freight)  FROM Northwind.dbo.Orders  GROUP BY YEAR(OrderDate)  UNION ALL  SELECT NULL, NULL, NULL, NULL, SUM(Freight)  FROM Northwind.dbo.Orders) AS DORDER BY  CASE WHEN OrderYear  IS NULL THEN 1 ELSE 0 END, OrderYear,  CASE WHEN OrderMonth IS NULL THEN 1 ELSE 0 END, OrderMonth,  CASE WHEN OrderDay   IS NULL THEN 1 ELSE 0 END, OrderDay,  CASE WHEN OrderID    IS NULL THEN 1 ELSE 0 END, OrderID;

Solution 2: use the GROUPING function instead of the above CASE
expression:

SELECT   YEAR(OrderDate)  AS OrderYear,  MONTH(OrderDate) AS OrderMonth,  DAY(OrderDate)   AS OrderDay,  OrderID,  SUM(Freight)     AS FreightFROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderIDWITH ROLLUPORDER BY  GROUPING(YEAR(OrderDate)) , OrderYear,  GROUPING(MONTH(OrderDate)), OrderMonth,  GROUPING(DAY(OrderDate))  , OrderDay,  GROUPING(OrderID)         , OrderID;

Solution 3: convert a NULL representing an aggregate to a higher value than
any possible value in the column:

SELECT   YEAR(OrderDate)  AS OrderYear,  MONTH(OrderDate) AS OrderMonth,  DAY(OrderDate)   AS OrderDay,  OrderID,  SUM(Freight)     AS FreightFROM Northwind.dbo.OrdersGROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderIDWITH ROLLUPORDER BY  ISNULL(YEAR(OrderDate) ,      10000),  ISNULL(MONTH(OrderDate),         13),  ISNULL(DAY(OrderDate)  ,         32),  ISNULL(OrderID         , 2147483647);

Solution 4: use the ROW_NUMBER() function to calculate a row number
based on (OrderDate, OrderID) ordering (call it RowNum); sort by
MAX(RowNum), OrderID DESC, OrderDay DESC, OrderMonth DESC,
OrderYear DESC:

WITH C AS(  SELECT OrderID, Freight,    YEAR(OrderDate) AS OrderYear,    MONTH(OrderDate) AS OrderMonth,    DAY(OrderDate) AS OrderDay,    ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum  FROM Northwind.dbo.Orders)SELECT OrderYear, OrderMonth, OrderDay, OrderID,  SUM(Freight) AS FreightFROM CGROUP BY OrderYear, OrderMonth, OrderDay, OrderIDWITH ROLLUPORDER BY  MAX(RowNum),  OrderID DESC, OrderDay DESC, OrderMonth DESC, OrderYear DESC;

A super aggregate will get the same MAX(RowNum) value as the one
calculated for the last base aggregate. Along with the other elements in the
ORDER BY list, this ensures that a supper aggregate will sort after a base
aggregate, and that a higher level super aggregate will sort after a lower level
one.

Cheers,
--
BG