least date in oracle sql

Question

Sample table:

Hi, can someone pl. help how do I get the least date and corresponding name from the table above? I should get only 3 rows, with first one as Ram and second one as def and third one as def.

Thanks

Answer 1

This is a special case of a top N per category style query, which can be implemented efficiently in Oracle.

Using Oracle's FIRST function

SELECT 
  id, 
  MIN(name)      KEEP (DENSE_RANK FIRST ORDER BY starttime) name,
  MIN(starttime) KEEP (DENSE_RANK FIRST ORDER BY starttime) starttime
FROM t
GROUP BY id

Other solutions include:

Window functions

SELECT id, name, starttime
FROM (
  SELECT 
    id, name, starttime, 
    ROW_NUMBER () OVER (PARTITION BY id ORDER BY starttime) rn
  FROM t
) t
WHERE rn = 1

Subqueries

As suggested by Yogesh, but they're way slower than the above solutions.

Answer 2

Use subquery or ranking function :

select * 
from table t
where StartTime = (select min(StartTime) from table where Id = t.Id);

You can also use row_number ranking function

select * 
from (select *,
             row_number() over (partition by Id order by StartTime) Seq
      from table t
     ) t
where Seq = 1;