CODEWITHSUNDEEP
sqlsql-server-2008selectsql-order-byunion
user987166
user987166

Reputation: 69

How to specify the order results with a SQL UNION?

I'm trying to run the following query:

select * from (select * from customquestionbank where questionid=6 or secondquestionid=6 
union select * from customquestionbank where questionid=5 or secondquestionid=5 
union select * from customquestionbank where questionid=10 or secondquestionid=10 
union select * from customquestionbank where questionid=11 or secondquestionid=11) Tabled

Being new to this site I cannot post images yet, but here is what the result look like:

questionid -> 5,6 ,10,11

However, I want the result to be displayed in the same order as my select statements above. In other words, questionid=6 returned first, then 5, and so on.

Upvotes: 2

Views: 247

Answers (5)

aF.
aF.

Reputation: 66687

You don't need all the unions, just make it like this:

SELECT DISTINCT * 
FROM   customquestionbank 
WHERE  questionid IN ( 6, 5, 10, 11 ) 
        OR secondquestionid IN ( 6, 5, 10, 11 ) 
ORDER  BY CASE 
            WHEN 6 IN ( questionid, secondquestionid ) THEN 0 
            WHEN 5 IN ( questionid, secondquestionid ) THEN 1 
            WHEN 10 IN ( questionid, secondquestionid ) THEN 2 
            WHEN 11 IN ( questionid, secondquestionid ) THEN 3 
          END

Upvotes: 2

Nilish
Nilish

Reputation: 1076

Replace Union with Union ALL

create table #t
(
    id int
)
insert into #t(id)values(5)
insert into #t(id)values(2)
insert into #t(id)values(4)
insert into #t(id)values(3)

Select * from
(
    Select * from #t where id = 5
    uNION All
    Select * from #t where id = 2
    uNION All
    Select * from #t where id = 4
    uNION All
    Select * from #t where id = 3
)K

DROP TABLE #t

Upvotes: 0

Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

Remove the super select that you are using

Write the query as

Select * from query1 union
Select * from query2 union
Select * from query3;

This will bring your result as needed

Else try this

Select col1,col2 from( Select 1,q1.* from query1 q1 union
Select 2,q2.* from query2 q2 union
Select 3,q1.* from query3 q3)

select only the required columns in the super query

Upvotes: 0

You Qi
You Qi

Reputation: 9211

if your RDBMS is mysql, I think you can try ORDER BY FIELD like this:

SELECT *
FROM customquestionbank
WHERE
  questionid IN(6, 5, 10, 11)
  OR secondquestionid IN(6, 5, 10, 11)
ORDER BY FIELD(questionid, 6) ASC

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453028

If your RDBMS supports the VALUES constructor

SELECT questionid, secondquestionid
FROM   (VALUES(6,1), 
              (5, 2), 
              (10, 3), 
              (11, 4)) V(q, ord) 
       JOIN customquestionbank 
         ON q IN ( questionid, secondquestionid ) 
GROUP BY questionid, secondquestionid         
ORDER  BY MIN(ord)

Upvotes: 0

Related Questions