Martijn -- Thanks for the question regarding "Top-N query over several years avoiding union ", version 9.2.0

You Asked

Hello Tom,

First of all: HAPPY NEW YEAR and many thanks for all your help in 2006.

I struggle with the following top-500 query, over multiple years:

SELECT to_char(year, 'rrrr') YEAR
,      (SELECT resp_nr FROM pan_Respondents WHERE ID = resp_id) resp_nr
,      amount
,      row_number() over ( ORDER BY amount DESC) rank    
FROM
(
SELECT trunc(cpn.creation_date, 'rrrr')  year
,      pmr.resp_id
,      SUM(nvl(cpn.amount,0)) amount
FROM   pan_compensations cpn
,      pan_panel_members pmr
,      pan_panels panl
WHERE  trunc(cpn.creation_date) 

<u>BETWEEN trunc(to_date('01-01-2001', 'dd-mm-yyyy'))
    AND trunc(to_date('31-12-2001', 'dd-mm-yyyy'))</u>

AND    cpn.pmr_id = pmr.ID
AND    pmr.panl_id =  panl.id
AND    panl.specific_comp_yn = 'N'
GROUP BY  trunc(cpn.creation_date, 'rrrr')
,         pmr.resp_id              
)
)
WHERE rank < 501
UNION
SELECT "YEAR","RESP_NR","AMOUNT","RANK" FROM
(
SELECT to_char(year, 'rrrr') YEAR
,      (SELECT resp_nr FROM pan_Respondents WHERE ID = resp_id) resp_nr
,      amount
,      row_number() over ( ORDER BY amount DESC) rank    
FROM
(
SELECT trunc(cpn.creation_date, 'rrrr')  year
,      pmr.resp_id
,      SUM(nvl(cpn.amount,0)) amount
FROM   pan_compensations cpn
,      pan_panel_members pmr
,      pan_panels panl
WHERE  trunc(cpn.creation_date) 

<u>BETWEEN trunc(to_date('01-01-2002', 'dd-mm-yyyy'))
    AND trunc(to_date('31-12-2002', 'dd-mm-yyyy'))</u>

AND    cpn.pmr_id = pmr.ID
AND    pmr.panl_id =  panl.id
AND    panl.specific_comp_yn = 'N'
GROUP BY  trunc(cpn.creation_date, 'rrrr')
,         pmr.resp_id              
)
)
WHERE rank < 501
UNION
...

etc, until current year ....

It is a top-n query, we want to report the first 500 respondents, ordered by the amount of compensation, over all years, starting with year 2001 until current year.

First the sum of compensation per respondent per year is calculated,
then the 500 most compensated respondents per year are selected using analytics.

As you can see, the year predicate is hard-coded.

I am wondering if there is a more elegant and intelligent way to achieve the same result, without using the union and hard-coded years.

Many thanks in advance for your valuable view in this matter.

Regards,

Martijn

and we said...

see

http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

you just want to partition by YEAR and assign row_number by year.

row_number() over
(partition by trunc(creation_date,'y')
order by amount desc ) rn


and use a where that gets all of the relevant dates - keeping rn <= 500

Reviews
Argh   January 2, 2007 - 8am Central time zone Bookmark | Bottom | Top Reviewer: Martijn from NLD Thanks for setting me straight. I should have known this. No Christmas wine for me anymore ;) Regards, Martijn

---

Write a Review