Oracle SQL, How can you do an order by abc without having abc in select?

Question

Select XYZ
FROM table
UNION ALL 
Select Sum(XYZ)
FROM table;

Placing an ORDER BY abc at the end is just giving me errors. I want to have the order done in the numbers in abc but not show the column in the report.

TheMadProfessor · Answer

If what you're trying to do is ensure the summation occurs last, try this:

SELECT XYZ, label AS "" FROM
(SELECT 1 AS OrderIt, XYZ, '"" AS label FROM table
UNION ALL
SELECT 2, SUM(XYZ), "(Total)" FROM table)
ORDER BY OrderIt

This sort of thing is about the only situation I've found where ordering by a non-selected column is useful.

Serge M · Answer

select xyz from (
Select XYZ, abc FROM table
UNION ALL 
Select Sum(XYZ) xyz, sum(abc) abc FROM table
) 
order by abc;

Oracle SQL, How can you do an order by abc without having abc in select?

2 Answers