Database: Oracle 10g
--Analytic function (1 table call)
SELECT a.id
FROM (SELECT id
,row_number() over(PARTITION BY id ORDER BY edate DESC) sub_rank
FROM tablea
WHERE edate <= Sysdate) a
WHERE a.sub_rank = 1;
--Subquery (two table calls)
SELECT p.id
FROM tablea p
WHERE p.edate =
(SELECT MAX(edate)
FROM tablea sb
WHERE sb.id = p.id
AND sb.edate <= Sysdate);
Both the queries return same results. Basically they get a list of ids.. only the ones that have max edate.
Does it really matter which way i choose except the table scan differences?