26

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)

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
Topera
  • 12,223
  • 15
  • 67
  • 104

7 Answers7

33

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
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
7

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.

Topera
  • 12,223
  • 15
  • 67
  • 104
  • If you want the "b" rows first, don't you want COUNT(id) DESC? Also, this only works if "b" is a subset of the records in "a". Finally, you can't rely on the records from "b" or "a" being ordered in any particular way, given this particular example. When I tried this, I got 1,2,4,3 for output. – DCookie May 17 '11 at 22:18
  • a) Yes, in this example I need DESC (I forgot to put). But in my app I need then in the end of table. b) no problem, in my app, table `b` is a subset of `a`. c) OK, doesn't matter in which order cames in originals selects. Count does the job :) – Topera May 17 '11 at 22:31
  • You're good to go then ;-) I figured you were getting the answer you needed, I mainly wanted to point out to others some things to be aware of in this solution. I think the use of COUNT here is slick. – DCookie May 17 '11 at 22:48
  • 1
    This solution will not work if table B contains values that are not in table A. – Allan May 19 '11 at 14:00
  • @Allan I don't think you're right... I dont'see where Bs not in As are excluded – Adriano Carneiro May 19 '11 at 16:54
  • 1
    @Adrian: They'll be included, but the count of them will be 1, so they'll be intermingled with the values from table A. – Allan May 19 '11 at 18:09
  • You got that right. Being honest, I never really fully captured the ordering OP wants... – Adriano Carneiro May 19 '11 at 19:13
  • I'll accept my own answer, because adrian answer give me error "not a SELECTed expression". Anyway, Thanks a lot! – Topera May 19 '11 at 23:27
4

@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

Roeland Van Heddegem
  • 1,623
  • 2
  • 20
  • 35
1
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;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

@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;
Allan
  • 17,141
  • 4
  • 52
  • 69
0
SELECT id FROM a -- returns 1,4,2,3
UNION
SELECT id FROM b -- returns 2,1
order by 2,1
Vlad Bochenin
  • 3,007
  • 1
  • 20
  • 33
Nur.B
  • 319
  • 2
  • 4
0
WIITH subq as (SELECT id FROM a ORDER BY id)
SELECT id FROM subq
UNION
SELECT id FROM b
Med Abdelfettah
  • 1
  • 1