How to select top 1 and ordered by date in Oracle SQL? [duplicate]

Question

There is a clear answer how to select top 1:

select * from table_name where rownum = 1

and how to order by date in descending order:

select * from table_name order by trans_date desc

but they does not work togeather (rownum is not generated according to trans_date):

... where rownum = 1 order by trans_date desc

The question is how to select top 1 also ordered by date?

Answer 1

Modern Oracle versions have FETCH FIRST:

select * from table_name order by trans_date desc
fetch first 1 row only

Answer 2

... where rownum = 1 order by trans_date desc

This selects one record arbitrarily chosen (where rownum = 1) and then sorts this one record (order by trans_date desc).

As shown by Ivan you can use a subquery where you order the records and then keep the first record with where rownum = 1in the outer query. This, however, is extremely Oracle-specific and violates the SQL standard where a subquery result is considered unordered (i.e. the order by clause can be ignored by the DBMS).

So better go with the standard solution. As of Oracle 12c:

select * 
from table_name 
order by trans_date desc
fetch first 1 row only;

In older versions:

select *
from
(
  select t.*, row_number() over (order by trans_date desc) as rn
  from table_name t
)
where rn = 1;