Using Order by With Union

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

SQL

ALTER proc [dbo].[SP_Med_SelectAllDisease_ActiveMatByMedID]
@P_MedID int
as 
begin
select Distinct EnglishName,NULL AS DiseaseName
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID
Order by EnglishName ASC
--group by activemat_name,Disease_name
union all
select Distinct NULL , Disease_name 
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID 
Order by Disease_name ASC
--group by activemat_name,Disease_name
end

that is throw the error

Msg 156, Level 15, State 1, Procedure SP_Med_SelectAllDisease_ActiveMatByMedID, Line 14
Incorrect syntax near the keyword 'union'.

how can i use Union with Order by ?
thanks in advanced...

Posted 5-Jan-12 2:43am

Yasser El Shazly

Updated 5-Jan-12 2:56am

v3

Add a Solution

3 solutions

Solution 1

Just remove the order by of the inner selection (Order by EnglishName ASC). The result would otherwise not be ordered at all. What would you otherwise expect as a result? The product of two ordered result sets pasted together?

First do the complete selection and after that you order it. If the EnglishName doesn't contain null values (and the same goes for DiseaseName in the other) it wouldn't even matter because the null values will stay together when ordering them. You could then just do:

SQL

ORDER BY EnglishName, DiseaseName

Another option could be to add 1 as union_id to the first select and 2 as union_id to the second select and first order on that.

By the way, did you notice that the names DiseaseName and Disease_name mismatch?

Good luck!

Posted 5-Jan-12 3:27am

E.F. Nijboer

Comments

Yasser El Shazly 5-Jan-12 9:33am

i do what you mentioned but this error appear when i try to add Order by in the end
Msg 104, Level 16, State 1, Procedure SP_Med_SelectAllDisease_ActiveMatByMedID, Line 20
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

E.F. Nijboer 5-Jan-12 11:07am

Isn't that error caused by the mismatching names DiseaseName and Disease_name?

Solution 3

In union you define only one order by which is applied for the whole result. Also in order by you can refer to columns by their position. And another note, you don't have to define distinct for both queries. If you use union without all, only distinct records are returned.

So an alternative could be like:

SQL

select  EnglishName,NULL AS DiseaseName
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID
union all
select NULL , Disease_name
from Med,Active_material,Active_mat_med,Diseases,Med_disease
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Diseases.Disease_ID=Med_disease.Disease_ID
and Med.Med_ID=Med_disease.Med_ID
and Med.Med_ID=@P_MedID
Order by 1

Posted 5-Jan-12 9:27am

Wendelius

Comments

Espen Harlinn 5-Jan-12 16:42pm

5'ed!

Wendelius 5-Jan-12 17:16pm

Thanks :)

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)

Prasad_Kulkarni · Accepted Answer · 2012-01-05T03:31:00

Solution 2

Hi,
Have a look to get best examples of UNION with ORDER BY;
http://www.techonthenet.com/sql/union.php[^]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90153[^]
http://www.java2s.com/Code/Oracle/Result-Set/AUNIONquerythatusesanORDERBYclause.htm[^]

Posted 5-Jan-12 3:31am

Prasad_Kulkarni