Return query results with union all in same order as the values in my `IN(…)` statement

Question

I want to order query below by IN value, like the result order by phone 4444,6666,5555, ...

(
  select tab_a_user as user, tab_a_phone as phone
  from table_a
  where tab_a_phone in (4444, 6666, 5555, ...)
)
UNION ALL
(
  select tab_b_user as user, tab_b_phone as phone
  from table_b
  where tab_b_phone in (4444, 6666, 5555, ...)
)

i tried to change my query like below, but seems missing right parenthesis

(
  select tab_a_user as user, tab_a_phone as phone
  from table_a
  where tab_a_phone in (4444, 6666, 5555, ...)
  order by field(phone,4444, 6666, 5555, ...))
UNION ALL
(
  select tab_b_user as user, tab_b_phone as phone
  from table_b
  where tab_b_phone in (4444, 6666, 5555, ...)
  order by field(phone,4444, 6666, 5555, ...))

value inside IN just an example, because in my program, i will put a variable, so it will have bunch of data

Use a case expression on the ORDER BY clause. – jarlh Nov 02 '17 at 07:42 — jarlh, Nov 02 '17 at 07:42

Indent · Accepted Answer · 2017-11-02T10:02:49.623

2

Another way using INSTR like this :

select * from  (

  select tab_a_user as user, tab_a_phone as phone
  from table_a
  where tab_a_phone in (4444, 6666, 5555)

  UNION ALL 

  select tab_b_user as user, tab_b_phone as phone
  from table_b
  where tab_b_phone in (4444, 6666, 5555)

)
order by
    INSTR('4444, 6666, 5555',phone)

to manage case with phone number included another phone number you need add delimiter :

order by
    INSTR('-4444-6666-5555-','-'||phone||'-')

edited Nov 02 '17 at 10:02

answered Nov 02 '17 at 08:08

Indent

4,675
1
19
35

1

Hi, This works for me, but seems as X makes my query error, so i remove the as X. Thanks ! – Novice Nov 02 '17 at 09:55

Indent · Answer 2 · 2017-11-02T10:02:31.997

You need provide an order mapping using case statement, then you can use order by on the expresion (after the UNION, you need use a nested query) :

select * from  (

  select tab_a_user as user, tab_a_phone as phone
  from table_a
  where tab_a_phone in (4444, 6666, 5555)

  UNION ALL 

  select tab_b_user as user, tab_b_phone as phone
  from table_b
  where tab_b_phone in (4444, 6666, 5555)

) as X
order by
( case
  when phone = 4444 then 1
  when phone = 6666 then 2
  when phone = 5555 then 3
  end
)

Or CTE Syntax :

with X as (

  select tab_a_user as user, tab_a_phone as phone
  from table_a
  where tab_a_phone in (4444, 6666, 5555)

  UNION ALL 

  select tab_b_user as user, tab_b_phone as phone
  from table_b
  where tab_b_phone in (4444, 6666, 5555)

)
select *
from X
order by
( case
  when phone = 4444 then 1
  when phone = 6666 then 2
  when phone = 5555 then 3
  end
)

If you have a table to force your order :

order_map.phone order_map.sort_value 
4444            1
6666            2
5555            3


with X as (

  select tab_a_user as user, tab_a_phone as phone
  from table_a
  where tab_a_phone in (4444, 6666, 5555)

  UNION ALL 

  select tab_b_user as user, tab_b_phone as phone
  from table_b
  where tab_b_phone in (4444, 6666, 5555)

)
select *
from X
    join order_map on
        X.phone = order_map.phone
order by
   order_map.sort_value

Hi, can i no use case ? cause the value inside IN just example, i will put a variable, so it will have bunch of value. — Novice, Nov 02 '17 at 07:55
You can use use a mapping table (using join) to provide your specific order — Indent, Nov 02 '17 at 07:57

score 1 · Answer 3 · answered Nov 02 '17 at 07:53

1

It won't scale well as it involves hardcoding, but using a ORDER BY clause and CASE you can do it:

select *
from
((select tab_a_user as user, tab_a_phone as phone 
 from table_a 
 where tab_a_phone in (4444, 6666, 5555)) 
UNION ALL 
(select tab_b_user as user, tab_b_phone as phone 
 from table_b 
 where tab_b_phone in (4444, 6666, 5555)))
ORDER BY
   CASE WHEN phone = 4444 THEN 1
        WHEN phone = 6666 THEN 2
        WHEN phonb = 5555 THEN 3 
   END

answered Nov 02 '17 at 07:53

Juan

3,675
20
34

Hi, can i no use case ? cause the value inside IN just example, i will put a variable, so it will have bunch of value – Novice Nov 02 '17 at 07:57
No you cannot, order by must be an expresion, you can build the case bit based on your variable – Juan Nov 02 '17 at 09:48

score 0 · Answer 4 · answered Nov 02 '17 at 08:32

Oracle Setup:

CREATE TABLE table_a ( tab_a_user, tab_a_phone ) AS
SELECT 'a1', 6666 FROM DUAL UNION ALL
SELECT 'a2', 4444 FROM DUAL;

CREATE TABLE table_b ( tab_b_user, tab_b_phone ) AS
SELECT 'b1', 3333 FROM DUAL UNION ALL
SELECT 'b2', 5555 FROM DUAL;

Query:

WITH orders ( idx, value ) AS (
  SELECT ROWNUM, COLUMN_VALUE
  FROM   TABLE( SYS.ODCINUMBERLIST( 4444, 6666, 5555 ) )
)
SELECT usr, phone
FROM   (
  SELECT idx,
         tab_a_user AS usr,
         tab_a_phone AS phone
  FROM   table_a a
         INNER JOIN orders o
         ON ( a.tab_a_phone = o.value )
  UNION ALL
  SELECT idx,
         tab_b_user,
         tab_b_phone
  FROM   table_b b
         INNER JOIN orders o
         ON ( b.tab_b_phone = o.value )
)
ORDER BY idx;

Output:

USR PHONE
--- -----
a2   4444
a1   6666
b2   5555

Return query results with union all in same order as the values in my `IN(…)` statement

4 Answers4