SQL how to use UNION and order by a specific select

Question

I have two selects   SELECT id FROM a -- returns 1 4 2 3 UNION SELECT id FROM b -- returns 2 1   I m receiving correct num of rows  like  1 4 2 3   But I want b table results first  2 1 4 3 or 2 1 3 4  How can I do this    I m using Oracle

User · Accepted Answer

Using  Adrian tips  I found a solution   I m using GROUP BY and COUNT  I tried to use DISTINCT with ORDER BY but I m getting error message   not a SELECTed expression   select id from        SELECT id FROM a -- returns 1 4 2 3     UNION ALL -- changed to ALL     SELECT id FROM b -- returns 2 1   GROUP BY id ORDER BY count id     Thanks Adrian and this blog

User · Answer

SELECT id  1 AS sort order   FROM b UNION SELECT id  2 AS sort order   FROM a MINUS SELECT id  2 AS sort order   FROM b ORDER BY 2

User · Answer

You want to do this   select   from        SELECT id  2 as ordered FROM a -- returns 1 4 2 3     UNION     SELECT id  1 as ordered FROM b -- returns 2 1   order by ordered   Update  I noticed that even though you have two different tables  you join the IDs  that means  if you have 1 in both tables  you are getting only one occurrence  If that s the desired behavior  you should stick to UNION  If not  change to UNION ALL   So I also notice that if you change to the code I proposed  You would start getting both 1 and 2  from both a and b   In that case  you might want to change the proposed code to   select distinct id from        SELECT id  2 as ordered FROM a -- returns 1 4 2 3     UNION     SELECT id  1 as ordered FROM b -- returns 2 1   order by ordered

User · Answer

Adrian s answer is perfectly suitable  I just wanted to share another way of achieving the same result   select nvl a id  b id  from a full outer join b on a id   b id order by b id

User · Answer

SELECT id FROM a -- returns 1 4 2 3 UNION SELECT id FROM b -- returns 2 1 order by 2 1

User · Answer

Adrien s answer is not working  It gives an ORA-01791   The correct answer  for the question that is asked  should be   select id from    SELECT id  2 as ordered FROM a -- returns 1 4 2 3   UNION ALL   SELECT id  1 as ordered FROM b -- returns 2 1     group by id order by min ordered    Explanation    The  UNION ALL  is combining the 2 sets  A  UNION  is wastefull because the 2 sets could not be the same  because the ordered field is different  The  group by  is then eliminating duplicates The  order by min  ordered   is assuring the elements of table b are first   This solves all the cases  even when table b has more or different elements then table a

[sql] SQL: how to use UNION and order by a specific select?

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to union