ORDER BY in UNION does not work

Question

In ORACLE DB I dont understand why ORDER BY sometimes work and sometimes does not. As well as I understand - the columns are always derived from the first subquery in the compound operator. In the case below we have last column amd that we sort by:

SELECT 'Ala' col1, 123123 sal, 0.33 amd FROM DUAL
UNION 
SELECT 'Ola' kol1, 12444 usd, 0.22 FROM DUAL
ORDER BY amd;

But adding extra union breaks everything:

SELECT 'Ala' col1, 123123 sal, 0.33 amd FROM DUAL
UNION 
SELECT 'Ola' kol1, 12444 usd, 0.22 FROM DUAL
UNION
SELECT 'Ula' coll1, 12444 eur, NULL FROM DUAL
ORDER BY amd;

ORDER BY amd
         *
ERROR at line 6:
ORA-00904: "AMD": invalid identifier

Even though amd is still the last column (SELECT without ORDER BY):

SELECT 'Ala' col1, 123123 sal, 0.33 amd FROM DUAL
UNION
SELECT 'Ola' kol1, 12444 usd, 0.22 FROM DUAL
UNION
SELECT 'Ula' coll1, 12444 eur, NULL FROM DUAL;

COL        SAL        AMD
--- ---------- ----------
Ala     123123        .33
Ola      12444        .22
Ula      12444

Why is it so?
I know I should be using aliases for all columns or numeric positions, but I am prepping for an exam and would like to understand the inconsistency. Note that parentheses do not solve the issue.

Every component used in the UNION should be aliased, just how Oracle works. You can report it as an Oracle bug if you want. — Jacek Trociński, Jun 13 '17 at 08:00
@dood. You can use positional sorting so it does not have to be. — displayName, Jun 13 '17 at 08:11
Yup, that would work too. I should have wrote, if you use an alias in the ORDER BY clause, you should alias each SELECT in the UNION as well. — Jacek Trociński, Jun 13 '17 at 08:13
`select * from ( ... here your query... ) t order by amd` works. Though, I don't know why 2 `UNION` not raises that error but more `UNION`'s does... — Oto Shavadze, Jun 13 '17 at 08:16
@displayName - Best not to get into a habit of using positional sorting. It makes large queries difficult to read and can cause issues if you change the order of the columns returned but forget to change the ORDER BY. — BriteSponge, Jun 13 '17 at 08:35

ORDER BY in UNION does not work

0 Answers0