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 challenge here.
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, 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                              
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 Freight                              FROM Northwind.dbo.Orders                              GROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderID                              WITH 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 Freight                              FROM Northwind.dbo.Orders                              GROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderID                              WITH ROLLUP                              ORDER 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 D                              ORDER 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 Freight                              FROM Northwind.dbo.Orders                              GROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderID                              WITH ROLLUP                              ORDER 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 Freight                              FROM Northwind.dbo.Orders                              GROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate), OrderID                              WITH ROLLUP                              ORDER 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 Freight                              FROM C                              GROUP BY OrderYear, OrderMonth, OrderDay, OrderID                              WITH ROLLUP                              ORDER 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