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.

The solution is to designate a special “Sort By” field in each query. Consider following revision of previous UNION

SELECT 'Suppliers' AS Name, 1 as SortBy
UNION
SELECT TOP (5) CompanyName, 2 FROM Suppliers
UNION
SELECT 'Products', 3
UNION
SELECT TOP (5) ProductName, 4 FROM Products
ORDER BY SortBy, Name

Here were added a numeric field to each query, 1 to the 1st, 2 to the 2nd, 3 to the 3rd and 4 to the 4th. And then in the ORDER BY clause we sort by this field first and the name second. The result is properly sorted list of suppliers & products:

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