MS SQL Server Concepts and Programming Question:

Download Questions PDF

Answer #1
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

Answer #2
<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.