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 →