Hey guys,

I am trying to create a query that will calculate each individual's average salary. The tricky part is I only want to average each individual's top 3 highest salaries in the history table.

I came close with this query:

SELECT mbr_ssn_nbr,
Floor(AVG(mbr_sal_amt)) as average_salary
from dsnp.pr01_t_mbr_hist
group by mbr_ssn_nbr
order by average_salary desc
fetch first 3 rows only



The problem here is that this query takes an average of each individual's entire salary history, and then gives me the top three highest average salaries. I need to use the Fetch command to retrieve each individual's top 3 salaries and then average that amount.

I'm sure it will have something to do with sub-queries which I'm not that comfortable with yet. Can anyone point me in the right direction? Thanks!

Consider using OLAP functions. Something like

I am completely new to the OLAP functions, and I can't get one to run without syntax errors. For example:

select mbr_ssn_nbr,
row_NUMBER() over (order by mbr_sal_amt desc) as test
from dsnp.pr01_t_mbr_hist
order by test


I get An unexpected token "<END-OF-STATEMENT>" was found following ""

What am I overlooking here?

Try "...order by 2" or use a subselect. You cannot refer to column aliases (the "test") in the same subselect.

What platform(OS) and DB2 version did you used?

Newer version(at least DB2 V9 for LUW) supports ORDER BY simple-column-name.