Brothers In Code

...a serious misallocation of .net resources

Selecting A Row With A Minimum Value

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.