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

I have two selects:

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

I'm recei开发者_Python百科ving 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)

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

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.

@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

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;

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

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

WIITH subq as (SELECT id FROM a ORDER BY id)
SELECT id FROM subq
UNION
SELECT id FROM b