I think everybody has had to do this a dozen times: return a row of data having a minimum value. The first reaction is to do a subquery in the where clause or as a derived table:
select *
from scan s
join (
select min(scan_date) as scan_date
from scan
) s_min on s.scan_date=s_min.scan_date
where rownum=1
A more performant solution is to use analytic functions:
select *
from (
select s.*, row_number() over (order by s.scan_date) rn
from scan s)
where rn = 1;
However, I thought this was a relatively simple and database agnostic solution:
select s1.*
from scan s1
left join scan s2 on s1.scan_date>s2.scan_date
where s2.scan_date is null
and rownum=1
This will return the row where no other row in the same table is older - in other words, the oldest row. This is probably the worst performer in the bunch but might be useful for an already complicated query.