Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Use ORDER BY with UNION Operators in MS SQL Server?
Submitted by: AdministratorIf 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
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
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
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.