Oracle 10g - SQL - Optimise query and call recursively

Question

I have a table containing information about records. There can be several drafts of each record. To query the info for the highest draft I currently do:

select 
    record_id,
    record_name,
    record_date
from 
    RECSTABLE
where 
    record_number = XXXXX
    AND record_draft = (select max(record_draft)
                        from RECSTABLE
                        where record_number = XXXXX)

This returns the correct results, but it's cumbersome. I want to do something more advanced.

I have a list or record_numbers I want to run this query for. The question is how can I do this recursively, how can I optimise this query?

Thanks

Use analytic functions - see Bluefeet's answer using row_number() to http://stackoverflow.com/questions/15234479/oracle-to-retrieve-maximum-record — Frank Schmitt, May 01 '14 at 10:31

score 2 · Answer 1 · answered May 01 '14 at 11:02

Don't use analytic functions for this, but use LAST aggregate function, because it's faster.

Something like this:

select max(record_id)   keep (dense_rank last order by record_draft) record_id
     , max(record_name) keep (dense_rank last order by record_draft) record_name
     , max(record_date) keep (dense_rank last order by record_draft) record_date
  from recstable
 where record_number = XXXXX
 group by record_number

score 1 · Accepted Answer · answered May 01 '14 at 11:04

How about something like this:

SELECT RECORD_ID,
       RECORD_NAME,
       RECORD_DATE
  FROM RECSTABLE r
  INNER JOIN (SELECT RECORD_NUMBER,
                     MAX(RECORD_DRAFT) AS MAX_RECORD_DRAFT
                FROM RECSTABLE
                GROUP BY RECORD_NUMBER) m
    ON (m.RECORD_NUMBER = r.RECORD_NUMBER)
  WHERE r.RECORD_NUMBER IN (xxxxx, yyyyy, zzzzz) AND
        r.RECORD_DRAFT = m.MAX_RECORD_DRAFT;

Share and enjoy.

score 0 · Answer 3 · answered May 01 '14 at 11:35

select record_id, record_name, record_date from ( select record_id, record_name, record_date , row_number() over ( partition by record_number order by record_draft desc ) as rn from RECSTABLE where record_number in (.....) ) where rn = 1

Oracle 10g - SQL - Optimise query and call recursively

3 Answers3