Hey guys,

I have a salary table called T_MBR_HIST:


SSN# MBR_SAL_AMT
_____ ____________



My goal is to retrieve an average of the employee's top 3 salary amounts in their history. I was able to use the FETCH and sort commands to return the top 3 salaries, but my logic fails when I try to average the results.

Here is my query:

SELECT AVG(A.MBR.SAL_AMT) FROM


(SELECT MBR_SAL_AMT FROM
DSNP.PR01_T_MBR_HIST
WHERE mbr_SSN_NBR = 444554444
ORDER BY MBR_SAL_AMT DESC
FETCH FIRST 3 ROWS ONLY ) AS A



It appears that DB2 doesn't like the order command inside my derived table. Any suggestions? Thanks!

Use OLAP functions, something like "...avg(sal_amt) over (partition by emp_id order by sal_amt desc rows 2 following)..."

Not tested - use your imagination.

Which version of DB2 are you using on which platform? Older versions of DB2 LUW didn't support ORDER BY in a subselect - newer versions do.

ya. I agree with Stolze. V9 Supports orer by in Subqueries too.

Mind posting what error you get in the exact terms as displayed by DB2....
Is MBR_SAL_AMT numeric???

Here is the error:

The use of the reserved word "ORDER" following "" is not valid.
Expected tokens may include: ") UNION EXCEPT". SQLSTATE=42601

SQL0199N The use of the reserved word "ORDER" following "" is not valid. Expected tokens may include: ") UNION EXCEPT


I believe that I am on DB2 8, but not sure.