How to sort results in UNION query in SQL Server
Looking for a solution to get the results in a UNION query in the order of the SELECT statements? Learn how to sort the results in a UNION statement.
Recently someone asked me how to sort the result set based on the order of the SELECT statements in a UNION query in SQL Server. Typically, you can use only a single ORDER BY statement for the entire query when multiple SELECT statements are combined using a UNION.
See this example:
SELECT DISTINCT name
FROM members WHERE Age = 20
UNION
SELECT DISTINCT name
FROM members WHERE Age = 30
UNION
SELECT DISTINCT name
FROM members WHERE Age = 40
ORDER BY Name
The above statement will fetch all records with the age 20, 30 or 40. The result will be sorted by the name. You cannot get it in the order of the select query.
How to sort the result set by the order of the SELECT statement in the UNION query?
SQL Server does not provide a built-in way to achieve the same. But here is a work around. You can define a constant and use that to sort.
Here is an example:
SELECT DISTINCT name, 1 AS Priority
FROM members WHERE Age = 20
UNION
SELECT DISTINCT name, 2 AS Priority
FROM members WHERE Age = 30
UNION
SELECT DISTINCT name, 3 AS Priority
FROM members WHERE Age = 40
ORDER BY Priority
The above query will return results in the order of the SELECT statements in the query.