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?
... 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; 
Modern Oracle versions have FETCH FIRST:
select * from table_name order by trans_date desc fetch first 1 row only There should be subquery so the combination rownum & order could work:
select * from (select * from table_name order by trans_date desc) AS tb where rownum = 1 You can use window functions for that:
select t.* from ( select *, min(trans_date) over () as min_date, max(trans_date) over () as max_date from the_table ) t where trans_date = min_date or trans_date = max_date; Another option would be to join on the derived table
select t1.* from the_table join ( select min(trans_date) over () as min_date, max(trans_date) over () as max_date from the_table ) t2 on t1.trans_date = t2.min_date or t1.trans_date = t2.max_date; Not sure which one would be faster, you need to check the execution plan