UNION is a very common operator used in SQL, it allows to combine result of multiple queries into one. Unfortunately if you want to sort this unified resultset sorting is applied to entire result at once. But what if you wan to sort queries-participants individually?

Let’s say you have 2 following queries from Northwind database:

SELECT TOP (5) CompanyName FROM Suppliers ORDER BY CompanyName

Aux joyeux ecclésiastiques
Bigfoot Breweries
Cooperativa de Quesos 'Las Cabras'
Escargots Nouveaux
Exotic Liquids

and

SELECT TOP (5) ProductName FROM Products ORDER BY ProductName

Alice Mutton
Aniseed Syrup
Boston Crab Meat
Camembert Pierrot
Carnarvon Tigers

And now you want to combine them into a single result, adding title lines to separate each result. The direct approach would be:

SELECT 'Suppliers' AS Name
UNION
SELECT TOP (5) CompanyName FROM Suppliers
UNION
SELECT 'Products'
UNION
SELECT TOP (5) ProductName FROM Products
ORDER BY Name

But result is far from what we wanted

Alice Mutton
Aniseed Syrup
Aux joyeux ecclésiastiques
Bigfoot Breweries
Boston Crab Meat
Camembert Pierrot
Carnarvon Tigers
Cooperativa de Quesos 'Las Cabras'
Escargots Nouveaux
Exotic Liquids
Products
Suppliers

Entire resultset is sorted uniformally and products are mixed with suppliers.
Continue reading →