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

@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:

1. 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.
2. The "group by" is then eliminating duplicates
3. 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

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.

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