How to use Union , Order By and Rownum?

Question

Requirement : I want to perform Union first. Then Order By and then select 1 row.

select * from v$version;

--Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 

Below is my table.

MyTable1

Amt | CreationDate
-----------------
100 |  01/01/2021
200 |  01/02/2021
50  |  10/01/2021

MyTable2

Amt | CreationDate
-----------------
100 |  01/01/2021
200 |  01/02/2021
50  |  10/01/2021

SELECT amt
  FROM (SELECT amt
        FROM MyTable1
        UNION
        SELECT amt
        FROM MyTable2
        ORDER BY CreationDate DESC)
 WHERE rownum = 1;

Error is : ORA-00904 : CreationDate invalid identifier

I have this query inside PL/SQL code.

Answer 1

To order by CreationDate, it needs to be part of your select

select amt
from (select amt, creationdate 
      from mytable
      union
      select cst_amt, creationdate 
      from mytable
      order by creationdate desc)
where rownum = 1;

Judging from your intention with the code, I think you could have just done

select greatest(max(amt), max(cst_amt))
from mytable
where cst_amt is not not and amt is not null;

Oracle 12c supports fetch clause, so you can tinker with that too.