Oracle analytic functions for "the attribute from the row with the max date"

Posted by tpdi on Stack Overflow See other posts from Stack Overflow or by tpdi
Published on 2010-04-27T00:10:47Z Indexed on 2010/04/27 0:23 UTC
Read the original article Hit count: 587

Filed under:
|
|

I'm refactoring a colleague's code, and I have several cases where he's using a cursor to get "the latest row that matches some predicate":

His technique is to write the join as a cursor, order it by the date field descending, open the cursor, get the first row, and close the cursor.

This requires calling a cursor for each row of the result set that drives this, which is costly for many rows. I'd prefer to be able to join, but what something cheaper than a correlated subquery:

select a.id_shared_by_several_rows, a.foo from audit_trail a
where a.entry_date = (select max(a.entry_date) 
                     from audit_trail b 
                     where b.id_shared_by_several_rows = a.id_shared_by_several_rows 
                     );

I'm guessing that since this is a common need, there's an Oracle analytic function that does this?

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle