Oracle analytic functions for "the attribute from the row with the max date"
- by tpdi
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?
