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