CODEWITHSUNDEEP
sqlsorting
user2675939
user2675939

Reputation: 371

Query Sort Order Issue

I have two queries that I further do a union on to get distinct results. Currently, they are ordered by name in alphabetical order.

Query 1:

 Corporate Comp D
 Corporate Comp E

Query 2:

 Corporate Comp A
 Corporate Comp B
 Corporate Comp D
 Corporate Comp E
 Corporate Comp G

So after union, the results are A B D E G. and its order by alphabetical order, but, I want it to order by first query, so basically I want the order to work like

Final Sort Query

 Corporate Comp D
 Corporate Comp E
 Corporate Comp A
 Corporate Comp B
 Corporate Comp G

Upvotes: 0

Views: 57

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

select      col

from       (          select distinct 1 as i,col from query1
            union all (select 2,col from query2 minus select 2,col from query1)
           ) t

 order by   i,col      
 ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

In that case, don't use UNION. Here is an alternative:

select qq.col
from ((select q.col, 1 as which
       from query1 q
      ) union all
      (select q.col, 2 as which
       from query2 q
       where not exists (select 1 from query1 q1 where q1.col = q.col)
      )
     ) qq
order by qq.which, qq.col;

Or, you could use aggregation:

select qq.col
from ((select q.col, 1 as which
       from query1 q
      ) union all
      (select q.col, 2 as which
       from query2 q
      )
     ) qq
group by qq.col
order by min(qq.which), qq.col;

Upvotes: 2

Mat Richardson
Mat Richardson

Reputation: 3606

You could try this:-

select 
    *
from 
(
    select col from query1
    union
    select col from query2
) d
order by 
    case when col in (select col from query1) then 0 else 1 end, 
    col

Upvotes: 0

Related Questions