Sql ralated Question

Question

1.00/5 (1 vote)

See more:

SQL-Server

can i used union all with where and order by clause in sql server 2008

Posted 23-Mar-12 5:56am

a20kush

Add a Solution

2 solutions

Solution 1

The syntax of UNION is given here.
http://msdn.microsoft.com/en-us/library/ms180026.aspx[^]
As seen from here the WHERE is not part of UNION statement.
Essentially UNION combines the results from two query expressions and UNION ALL combines all rows from both the query expressions including duplicate rows as explained here
http://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp[^]

So UNION can be used with query expressions containing WHERE clause, but the WHERE is not part of the UNION statement.

As Espen Harlinn said in his solution the ORDER BY clause sorts the full result set.

The order of ORDER BY is important as shown in the first link at
C. Using UNION of two SELECT statements with ORDER BY, with sample of both incorrect and correct placement of ORDER BY clause

Posted 23-Mar-12 6:13am

ProEnggSoft

Updated 23-Mar-12 6:45am

v3

Comments

Espen Harlinn 23-Mar-12 12:46pm

Have another look at my solution, the 'where' clauses are part of their respective select statements - it's only the 'order by' clause that works on the whole result set.

ProEnggSoft 23-Mar-12 12:48pm

Yes, I noticed it. I also stated same thing. Thank you.

Espen Harlinn 23-Mar-12 12:50pm

And it's now a very good answer - have a 5 :-)

ProEnggSoft 23-Mar-12 13:10pm

Thank you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Espen Harlinn · Accepted Answer · 2012-03-23T06:24:00

Solution 2

Like this:

SQL

SELECT NAME FROM T1
 WHERE NAME LIKE 'O%'
UNION ALL
SELECT NAME FROM T2
 WHERE NAME LIKE 'A%' 
ORDER BY NAME

The ORDER BY clause sorts the full result set, so elements from T2 will be listed ahead of elements from T1 - assuming there are elements that starts with A in T2 and elements that starts with O in T1.

Best regards
Espen Harlinn

Posted 23-Mar-12 6:24am

Espen Harlinn

Comments

ProEnggSoft 23-Mar-12 12:39pm

+5. You are absolutely correct. I have modified my answer based on your solution.

Espen Harlinn 23-Mar-12 13:11pm

Thanks :-)

Abhinav S 26-Mar-12 21:49pm

5!

Espen Harlinn 27-Mar-12 5:24am

Thank you Abhinav!