Collections:
Using ORDER BY with UNION Operators in SQL Server
How To Use ORDER BY with UNION Operators in SQL Server?
✍: FYIcenter.com
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:
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 and GROUP BY Clauses in SQL Server
⇐ UNION - Merging Outputs from Two Queries Together in SQL Server
⇑ Using SELECT Statements with Joins and Subqueries in SQL Server
2016-10-26, 1958🔥, 0💬
Popular Posts:
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
Where to find answers to frequently asked questions I am new to Oracle database. Here is a list of f...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...