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