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;
Advertisement
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>