Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Using ORDER BY with UNION Operators

By: FYIcenter.com

(Continued from previous topic...)

How To Use ORDER BY with UNION Operators

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.

1. How To Join Two Tables in a Single Query?
2. How To Write a Query with an Inner Join?
3. How To Define and Use Table Alias Names?
4. How To Write a Query with a Left Outer Join?
5. How To Write a Query with a Right Outer Join?
6. How To Write a Query with a Full Outer Join?
7. How To Write an Inner Join with the WHERE Clause?
8. How To Name Query Output Columns?
9. What Is a Subquery in a SELECT Query Statement?
10. How To Use Subqueries with the IN Operators?
11. How To Use Subqueries with the EXISTS Operators?
12. How To Use Subqueries in the FROM Clause?
13. How To Count Groups Returned with the GROUP BY Clause?
14. How To Return the Top 5 Rows from a SELECT Query?
15. How To Return the Second 5 Rows?
16. How To Use UNION to Merge Outputs from Two Queries Together?
17. How To Use ORDER BY with UNION Operators