Using ORDER BY to pair values in SQL ORACLE

I need to use ORDER BY in a query that uses UNION.

The query returns the following:

CRN	Inv_1	Inv_2
GBP	-100	-100
GBP	-400	-400
GBP	-500	-500
GBP	-700	-700
GBP	-800	-800
GBP	-10000	-10000
GBP	100	100
GBP	400	400
GBP	500	500
GBP	700	700
GBP	800	800
GBP	10000	10000

Expected Output:

CRN	Inv_1	Inv_2
GBP	100	100
GBP	-100	-100
GBP	400	400
GBP	-400	-400
GBP	500	500
GBP	-500	-500
GBP	700	700
GBP	-700	-700

I don’t have an ID to select and order by it and even if I have, I can’t use it since it will appear in the table which I can’t include in the month-end report.

These values will keep changing so they can’t be hard coded to use case statements in order. I tried so many options such as:

 ORDER BY Inv_1 DESC, Inv_1 ASC
 ORDER BY Inv_1 ASC, Inv_1 DESC
 ORDER BY Inv_1 ASC, Inv_2 DESC
 ORDER BY Inv_1 DESC, Inv_2 DESC

If I use ABS(Inv_1 DESC), Inv_1 . This throws an error: ORDER BY ITEM MUST BE THE NUMBER OF A SELEC-LIST EXPRESSION. I’m not using ABS but *-1 instead, since ABS returns the wrong Inv_1/Inv_2

Example:

**Using ABS with UNION doesn’t work : error: ORDER BY ITEM MUST BE THE NUMBER OF A SELEC-LIST EXPRESSION. **

select CRN,inv_1,inv_2 from Acc UNION select CRN,inv_1,inv_2 from Acc ORDER BY CRN, ABS(inv_1),inv_1 DESC;

Answer

You didn’t post your query, but – this kind of works for me.

Sample data:

SQL> select * from test;

CRN      INV_1      INV_2
--- ---------- ----------
GBP       -700       -700
GBP        100        100
GBP        700        700
GBP       -100       -100
GBP       -500        500
GBP        400        400
GBP        500        500
GBP       -400       -400

8 rows selected.

Query with order by clause:

SQL> select *
  2  from test
  3  order by crn, abs(inv_1), inv_1 desc;

CRN      INV_1      INV_2
--- ---------- ----------
GBP        100        100
GBP       -100       -100
GBP        400        400
GBP       -400       -400
GBP        500        500
GBP       -500        500
GBP        700        700
GBP       -700       -700

8 rows selected.

SQL>

---

[EDIT]

Aha, now I understand (although you still didn’t post your query).

If there are two tables which return the result set:

SQL> select * from test;

CRN      INV_1      INV_2
--- ---------- ----------
GBP       -700       -700
GBP        100        100
GBP        700        700
GBP       -100       -100

SQL> select * from test2;

CRN      INV_1      INV_2
--- ---------- ----------
GBP       -500        500
GBP        400        400
GBP        500        500
GBP       -400       -400

SQL>

Then – if you UNION them and apply order by something that isn’t part of the query – it won’t work:

SQL> select crn, inv_1, inv_2
  2  from test
  3  union
  4  select crn, inv_1, inv_2
  5  from test2
  6  order by crn, abs(inv_1), inv_1 desc;
order by crn, abs(inv_1), inv_1 desc
              *
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression


SQL>

What can you do? Obviously, add abs(inv_1) into the query:

SQL> select crn, inv_1, inv_2, abs(inv_1) abs_inv1
  2  from test
  3  union
  4  select crn, inv_1, inv_2, abs(inv_1) abs_inv1
  5  from test2
  6  order by crn, abs_inv1, inv_1 desc;

CRN      INV_1      INV_2   ABS_INV1
--- ---------- ---------- ----------
GBP        100        100        100
GBP       -100       -100        100
GBP        400        400        400
GBP       -400       -400        400
GBP        500        500        500
GBP       -500        500        500
GBP        700        700        700
GBP       -700       -700        700

8 rows selected.

SQL>

This works, but – now you have additional column which you don’t really want in the output.

Workaround is to use a CTE or a subquery which contains UNION, but then – in the outer query – you can sort by abs value:

SQL> with temp as
  2    (select crn, inv_1, inv_2
  3     from test
  4     union
  5     select crn, inv_1, inv_2
  6     from test2
  7    )
  8  select crn, inv_1, inv_2
  9  from temp
 10  order by crn, abs(inv_1), inv_1 desc;

CRN      INV_1      INV_2
--- ---------- ----------
GBP        100        100
GBP       -100       -100
GBP        400        400
GBP       -400       -400
GBP        500        500
GBP       -500        500
GBP        700        700
GBP       -700       -700

8 rows selected.

SQL>