How to select top 1 and ordered by date in Oracle SQL? [duplicate]
... where rownum = 1 order by trans_date descThis 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 descfetch 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;