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

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?

@MT0 , this is not a duplicate of **"How do I do top 1 in Oracle?"** and **"Oracle SELECT TOP 10 records"** because they are just about limited number of rows in result of a query, and not about ordering. As you can see I refer to the first question in the first line of mine. — Ivan Gerasimenko, Jun 08 '17 at 13:08
"How do I do top 1 in Oracle?" all the answers apart from the accepted one show how to order the result set and get the first ordered result. "Oracle SELECT TOP 10 records" the OP explicitly asks why they are getting random rows rather than the top ordered rows. They are duplicates. — MT0, Jun 08 '17 at 13:11
BTW, I can not delete the question because it's prohibited by system: some people have already answered it — Ivan Gerasimenko, Jun 08 '17 at 13:12

score 34 · Accepted Answer · answered Jun 08 '17 at 09:03

... 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;

score 2 · Answer 2 · answered Jun 08 '17 at 09:00

2

Modern Oracle versions have FETCH FIRST:

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

answered Jun 08 '17 at 09:00

jarlh

35,821
8
33
49

Ivan Gerasimenko · Answer 3 · 2017-06-08T08:44:20.033

1

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

edited Jun 08 '17 at 08:44

answered Jun 08 '17 at 08:39

Ivan Gerasimenko

2,138
2
20
44

score 1 · Answer 4 · answered Jun 08 '17 at 08:45

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

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

4 Answers4

Related