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

Submitted by: Administrator
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 ggl_links WHERE tag = 'DBA'
ORDER BY created)
UNION
(SELECT * FROM ggl_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 ggl_links WHERE tag = 'DBA')
UNION
(SELECT * FROM ggl_links WHERE tag = 'DEV')
ORDER BY created
GO
Submitted by: Administrator

<pre>
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 globalguideline.com NULL 120 2006-04-30 DEV
102 globalguideline.com/sql NULL 972 2007-05-19 DBA
</pre>
Note that the ORDER BY works nicely. Rows returned from both subqueries are sorted together.
Submitted by: Administrator

Read Online MS SQL Server Job Interview Questions And Answers