Order By On A Union?

Facebooktwitterredditpinterestlinkedinmail

Have you ever tried to add an ORDER BY clause to a UNION operator and have gotten this error message?

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

UNION

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

So what the heck is this and how do we fix it?

 
What The Error Says
The error basically says that you have an issue with your syntax. The ORDER BY clause should only be applied one time in your query. Logically SQL Server will pull the data requested from the two tables… then it will perform the sort operation on the data.

When people see the UNION operator, they think that it is combining two different result sets, thus they need two ORDER BY clauses. In fact, you only need to order the final result set. This is done by placing the ORDER BY command at the very end of the statement.

SELECT AnimalName
FROM Animal
-- Notice the removed ORDER BY here?

UNION

SELECT AnimalName
FROM Animal
ORDER BY AnimalName