Use ROLLUP with ORDER BY clause moves the Grand Total row from the bottom to the top

Here’s the schema:

This works:

However, when I add an ORDER BY clause, the Grand Total row moves from the bottom. I understand the first example has the rows sorted by Genre but I wonder why adding the clause breaks it.

Answer

You can use multiple keys for the order by:

select coalesce(Genre, 'Grand Total'), sum(TotalUnits) as total_units_sold
from album 
group by Rollup (Genre) 
order by (case when genre is null then 1 else 2 end) desc,
         genre;