The union did in fact get the exact right results. And was the clearest in what it was doing. Then someone observed that the main table was already an aggregate of results from several other tables, built just for running this one report. There are no other joins against it. The right way for us to do this is check and update (if necessary) the value of a flag in the main table every time we update an escalation.

All the other solutions suffer from the same problem. In order to get the correct data we'd have to order a specific way before grouping. But if that isn't the way we want the output ordered, we end up re-sorting in code anyway.