Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

analytic function vs subquery

ora1001Jun 12 2012 — edited Jun 12 2012
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?
This post has been answered by sb92075 on Jun 12 2012
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 10 2012
Added on Jun 12 2012
2 comments
1,110 views