CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Sharif Mia
Sharif Mia

Reputation: 111

Mix the result of union all query and order them by row by row

I apologize if the title of the question is unclear.

I have this sample query: 

SELECT a.i_id,
       a.i_item,
       a.i_count
  FROM abc a
 WHERE i_id = 123
UNION ALL
SELECT b.j_id,
       b.j_item,
       b.j_count
  FROM def b
 WHERE j_id = 4567

which returns the result in the screenshot 1. enter image description here

I want to modify the query so it can return what is shown in the screenshot 2. enter image description here

Upvotes: 2

Views: 192

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Without an ORDER BY clause, the order of a query result is completely left to chance. If the orange part of the result stems from the first query and the blue one from the second, this is in no way guaranteed.

So add an ORDER BY clause at the end of your query. It is not clear by what attribute you want to sort. Here are some possibilities:

Option 1: Keep same items together, always first one id then the other.

...
ORDER BY i_item, i_id;

Option 2: Keep same counts together, always first one ID then the other

...
ORDER BY i_count, i_id;

Option 3: Alternate between IDs, start with first items, then second ones, ...

ORDER BY row_number() over (partition by i_id order by i_item), i_id;

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Create a view and Use the following simple query, alternatively:

CREATE VIEW ghi AS
SELECT a.i_id,
       a.i_item,
       a.i_count
  FROM abc a
 WHERE i_id = 123
UNION ALL
SELECT b.j_id,
       b.j_item,
       b.j_count
  FROM def b
 WHERE j_id = 4567;  

SELECT *
  FROM ghi
 ORDER BY  i_item, i_id;

SQL Fiddle Demo

Upvotes: 0

MT0
MT0

Reputation: 167982

Assuming, in your real example, you can't just ORDER BY i_item or ORDER BY i_count to get your desired order then:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE abc ( i_id, i_item, i_count ) AS
SELECT 123, 'apple',  100 FROM DUAL UNION ALL
SELECT 123, 'banana', 200 FROM DUAL UNION ALL
SELECT 123, 'candy',  300 FROM DUAL;

CREATE TABLE def ( j_id, j_item, j_count ) AS
SELECT 4567, 'apple',  100 FROM DUAL UNION ALL
SELECT 4567, 'banana', 200 FROM DUAL UNION ALL
SELECT 4567, 'candy',  300 FROM DUAL;

Query 1:

SELECT i_id,
       i_item,
       i_count
FROM   (
  SELECT i_id,
         i_item,
         i_count,
         ROWNUM,
         1
    FROM abc
   WHERE i_id = 123
  UNION ALL
  SELECT j_id,
         j_item,
         j_count,
         ROWNUM,
         2
    FROM def
   WHERE j_id = 4567
  ORDER BY 4, 5
)

Results:

| I_ID | I_ITEM | I_COUNT |
|------|--------|---------|
|  123 |  apple |     100 |
| 4567 |  apple |     100 |
|  123 | banana |     200 |
| 4567 | banana |     200 |
|  123 |  candy |     300 |
| 4567 |  candy |     300 |

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

DERIVED TABLE SORTING

select * from 
(    SELECT a.i_id,
           a.i_item,
           a.i_count
      FROM abc a
     WHERE i_id = 123
    UNION ALL
    SELECT b.j_id,
           b.j_item,
           b.j_count
      FROM def b
     WHERE j_id = 4567
) X
order by i_item

Upvotes: 1

Related Questions