How To Use ORDER BY with UNION Operators in SQL Server?

If you need to sort the output from two queries grouped together with a UNION operator, you need to apply the ORDER BY clause at the group level, not at the subquery level.

Note that SQL Server and MySQL react differently to the ORDER BY clause used in a subquery of a UNION operator:

• SQL Server will give error if ORDER BY is used inside a subquery of a UNION operator.
• MySQL will ignore the ORDER BY clause inside a subquery of a UNION operator.

The following tutorial exercise shows you how to use ORDER BY clause with UNION operator:

(SELECT * FROM fyi_links WHERE tag = 'DBA'
   ORDER BY created)
UNION
(SELECT * FROM fyi_links WHERE tag = 'DEV'
   ORDER BY created)
GO
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.

(SELECT * FROM fyi_links WHERE tag = 'DBA')
UNION
(SELECT * FROM fyi_links WHERE tag = 'DEV')
ORDER BY created
GO
id   url                notes  counts  created     tag
106  www.php.net               439     2004-01-01  DEV
105  www.oracle.com            960     2005-01-01  DBA
104  www.mysql.com             390     2006-01-01  DBA
101  dev.fyicenter.com  NULL   120     2006-04-30  DEV
102  dba.fyicenter.com  NULL   972     2007-05-19  DBA

Note that the ORDER BY works nicely. Rows returned from both subqueries are sorted together.

⇒Using SELECT Statements with Joins and Subqueries in SQL Server

⇒⇒SQL Server Database Tutorials