Analytic functions for "the attribute from the row with the max date"

Question

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?

Answer 1

This will only do one pass over the data, and may be used to get as many columns from the table as needed without doing a self-join.

select DISTINCT
       a.id_shared_by_several_rows,
       FIRST_VALUE(a.foo)
       OVER (PARTITION BY a.id_shared_by_several_rows
             ORDER BY a.entry_date DESC)
       AS foo
from audit_trail a;

Answer 2

There are analytics RANK, DENSE_RANK and ROW_NUMBER for identifying the sequence number of a row according to a sort criteria. They differ in how they handle rows that do not differ in the order columns. [Eg you may get 1,1,3 or 1,1,2 or 1,2,3.]

select index_name, column_name, column_position,
       rank() over (partition by table_name order by column_position) rnk,
       dense_rank() over (partition by table_name order by column_position) drnk,
       row_number() over (partition by table_name order by column_position) rn
from all_ind_columns
where index_owner = 'SYSMAN'
and table_name = 'MGMT_JOB_EXECUTION';

Because analytics operate on the selected rows, you still need a subquery/inline view to filter out the ones you don't want. In this example, INDEX_NAME is the shared identifier

select index_name, column_name
from
  (select index_name, column_name, column_position,
         row_number() over (partition by index_name order by column_position) rn
  from all_ind_columns
  where index_owner = 'SYSMAN'
  and table_name = 'MGMT_JOB_EXECUTION')
where rn = 1;

Answer 3

I believe you want to use

select
  max(id_shared_by_several_rows) keep (dense_rank first order by entry_date),
  max(foo                      ) keep (dense_rank first order by entry_date)
from
  audit_trail;

Answer 4

Try this:

select id_shared_by_several_rows, foo from (
select a.id_shared_by_several_rows, a.foo, a.entry_date, max(a.entry_date) over (partition by a.id_shared_by_several_rows) max_entry_date
from audit_trail_a
) where entry_date = max_entry_date