Amir, I believe that you can do it with DB2 on UWO using the ranking function and a common table expresion. I don't believe the same facility exists on the mainframe.
If you are on UWO, let me know and I will dig out the correct syntax and post it. It was certainly covered in a presentation at the European IDUG in Florence recently. Cheers, Jeremy Amir Sadeghi wrote: > Sorry....I was not accurate in my original problem description. > > In fact, what I'd like to to is to have the top 2 money makers for each > department. Something like the following: > > Dept# Employee Num Salary Amount > > abc 12345 60K > abc 67890 58k > def 33333 62K > def 12098 61k > ghi 20485 53K > ghi 20484 52K > > Amir > > >From: "Arnoud W. Morsink" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: [EMAIL PROTECTED] > >Subject: Re: DB2EUG: Getting Top n using SQL > >Date: Fri, 9 Nov 2001 15:23:10 -0800 (PST) > > > > > Give me the 2 highest salary_amounts (along with the employee_num) in > >the > > > department table. > > > > > > EMPLOYEE_NUM SALARY_AMOUNT > > > > > > 123456 60k > > > 983474 58K > > > >Again, it's been a while, but I believe the following should work: > > > >SELECT * FROM department_table > >ORDER BY SALARY_AMOUNT > >FETCH FIRST 2 ROWS ONLY > > > > > >-- > >Arnoud > > > > > >===== > >To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] > >For other info (and scripts), see > >http://people.mn.mediaone.net/scottrmcleod > > > > _________________________________________________________________ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > > ===== > To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] > For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod ===== To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
