Top N records for each X

[MikeAuz1979]

Hi,

Using an access pass through query to oracle (Native oracle pl sql) I'm trying to return the 2 most recent records (Max PRICE_EFFECTIVE_DATES) for each contract. (SUBCONTRACT is the field in my code.)

So the result I'm after is like:

SUBCONTRACT P_E_DATE P_P_B
1 01-jan-07 5.67
1 01-jul-07 5.87
2 23-feb-07 4.78
2 30-mar-07 4.99


Using some code I found from another website I've gotten as far as the below but I'm getting the error that the sql is not properly ended.

Also any ideas on how I can avoid repeating the sub query code?

Thanks for any help
Mike

select t1.SUBCONTRACT_NUMBER
     , t1.PRICE_EFFECTIVE_DATE
     , t1.PRICE_PER_BLOCK
  from 
(
SELECT sub.SUBCONTRACT_NUMBER, sd.PRICE_EFFECTIVE_DATE, bp.PRICE_PER_BLOCK

FROM CCDR.BLOCK_PRICE bp, CCDR.SUBCONTRACT sub, CCDR.SUBCONTRACT_DETAILS sd, CCDR.ACTIVE_SITE a

WHERE sd.CHARGE_TYPE='04' 
AND sd.COMPONENT_TYPE='1'
AND sub.SUBCONTRACT_NUMBER = sd.SUBCONTRACT_NUMBER
AND bp.PRICE_ID = sd.PRICE_ID
AND sub.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
) as t1
inner
  join 
(
SELECT sub.SUBCONTRACT_NUMBER, sd.PRICE_EFFECTIVE_DATE, bp.PRICE_PER_BLOCK

FROM CCDR.BLOCK_PRICE bp, CCDR.SUBCONTRACT sub, CCDR.SUBCONTRACT_DETAILS sd, CCDR.ACTIVE_SITE a

WHERE sd.CHARGE_TYPE='04' 
AND sd.COMPONENT_TYPE='1'
AND sub.SUBCONTRACT_NUMBER = sd.SUBCONTRACT_NUMBER
AND bp.PRICE_ID = sd.PRICE_ID
AND sub.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
) as t2
    on t1.SUBCONTRACT_NUMBER = t2.SUBCONTRACT_NUMBER
   and t1.PRICE_EFFECTIVE_DATE <= t2.PRICE_EFFECTIVE_DATE
group by 
       t1.SUBCONTRACT_NUMBER
     , t1.PRICE_EFFECTIVE_DATE
     , t1.PRICE_PER_BLOCK
having count(*) <= 2

 

[SantaMufasa]

Mike,

Your need is perfectly suited for Oracle's Analytic Functions. Since I didn't have data to replicate your tables, I used the data you posted and placed it in one table for the sake of illustration:

SQL> select * from subcontract_details;

SUBCONTRACT_NUMBER PRICE_EFF PRICE_PER_BLOCK
------------------ --------- ---------------
                 1 01-JAN-07            5.67
                 1 01-JUL-07            5.87
                 1 01-JUL-06            4.87
                 1 01-JUL-05            3.87
                 2 21-FEB-07            2.78
                 2 22-FEB-07            3.78
                 2 23-FEB-07            4.78
                 2 30-MAR-07            4.99

Here is an example of an Analytic-Function query the does what you want:

select subcontract_number, price_effective_date, price_per_block
  from (select subcontract_number
              ,price_effective_date
              ,price_per_block
              ,rank () over (partition by subcontract_number
                                 order by price_effective_date desc) rnk
          from subcontract_details)
 where rnk < 3
/

SUBCONTRACT_NUMBER PRICE_EFF PRICE_PER_BLOCK
------------------ --------- ---------------
                 1 01-JUL-07            5.87
                 1 01-JAN-07            5.67
                 2 30-MAR-07            4.99
                 2 23-FEB-07            4.78
************************************************************************

Let us know if you have questions or follow-up issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

...And if you are not familiar with Analytics, here is your code tailored to use such:

select t1.SUBCONTRACT_NUMBER
      ,t1.PRICE_EFFECTIVE_DATE
      ,t1.PRICE_PER_BLOCK
  from (SELECT sub.SUBCONTRACT_NUMBER
              ,sd.PRICE_EFFECTIVE_DATE
              ,bp.PRICE_PER_BLOCK
              ,rank () over (partition by sub.subcontract_number
                                 order by  sd.price_effective_date desc) rnk
          FROM CCDR.BLOCK_PRICE bp
              ,CCDR.SUBCONTRACT sub
              ,CCDR.SUBCONTRACT_DETAILS sd
              ,CCDR.ACTIVE_SITE a
         WHERE sd.CHARGE_TYPE='04' 
           AND sd.COMPONENT_TYPE='1'
           AND sub.SUBCONTRACT_NUMBER = sd.SUBCONTRACT_NUMBER
           AND bp.PRICE_ID = sd.PRICE_ID
           AND sub.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER) as t1
 where rnk < 3;

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry...The following is to widen the display of the above code to avoid confusing line wraps:

*****************************************************************************

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MikeAuz1979]

Santa thats great and I'm sure it would work fine if we were using a current version of Oracle, I think the trouble I'm having is that were using an older version that doesn't support the rank function.

You've already been heaps of help but would you have an idea how to do this in older oracle versions?

Thanks
Mike

 

[SantaMufasa]

How old are we talking? It works on Oracle 8i.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]