user2675939 user2675939 - 26 days ago 10
SQL Question

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

Answer

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;
Comments