CODEWITHSUNDEEP
sqloracle-databaseunionsql-order-by
Nital
Nital

Reputation: 6084

How can you order by the result of union of two queries in Oracle?

I have the following Oracle SQL which works fine but the result of the second query gets returned in first row instead of second.

How can I maintain the sequence in the output so that the first query result gets displayed in the first row, second in the second row and so on.

Oracle SQL

select
  sum(a.transaction_amount) as transaction_amount,
  'Last 30 Days Debit Volume (Current Year)' as sales_volume
from
  payment_transaction a,
  payment_settlement b
where
  a.transaction_status = 'S'
  and b.settlement_type = 'D'
  and trunc(b.transaction_date) > sysdate - 30
  and a.payment_transaction_id = b.payment_transaction_id

union

select
  sum(a.transaction_amount) as transaction_amount,
  'Last 30 Days Credit Volume (Current Year)' as sales_volume
from
  payment_transaction a,
  payment_settlement b
where
  a.transaction_status = 'S'
  and b.settlement_type = 'C'
  and trunc(b.transaction_date) > sysdate - 30
  and a.payment_transaction_id = b.payment_transaction_id

Current Output

TRANSACTION_AMOUNT       SALES_VOLUME
6272                     Last 30 Days Credit Volume (Current Year)
10719                    Last 30 Days Debit Volume (Current Year)

Expected Output

TRANSACTION_AMOUNT       SALES_VOLUME
10719                    Last 30 Days Debit Volume (Current Year)
6272                     Last 30 Days Credit Volume (Current Year)

Upvotes: 0

Views: 2050

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Just add a dummy column to sort

 SELECT <yourfields>
 FROM (
      SELECT 1 as dummy, <yourfields>
      FROM Query1
      UNION
      SELECT 2 as dummy, <yourfields>
      FROM Query2
     ) T
ORDER BY dummy

Upvotes: 4

Related Questions