"N"th maximum Salary and SQL Query.

[HafeezAkmal]

Hi,

I was just searching for a QUERY which gives me the Nth Max sal of an employee. And found this:

Code: Select all

select max(sal) 
from emp 
where level = 10 
connect by prior sal > sal 
start with sal = (select max(sal) from emp)

I'm confused how this query works? Can somone please help me with this.

[zprogrammer]

What is the DB2 version at your shop?

And what happened when you tried to execute this as a DB2 query?

[Anuj Dhawan]

Those are Oracle constructs and this is an IBM DB2 part of the Forum, so I'll assume that you're looking forward to a DB2 solution, are you?

[HafeezAkmal]

Thanks Anuj. Yes, I need the answer for DB2. Please help.

We're at DB2 10. I can not access the mainframe as of now but the error was something that keyword is not recognized.

[Anuj Dhawan]

Try:

Code: Select all

SELECT *                                         
FROM EMPLOYEE E1                              
      WHERE 10 =                                  
      (SELECT COUNT(DISTINCT E2.SALARY) FROM     
       EMPLOYEE E2 WHERE E2.SALARY>=E1.SALARY)

With DB2 10, you can make use of FETCH FIRST N ROWs also.

[zprogrammer]

IIRC , You could generate the row id...

[Anuj Dhawan]

Right, and DB2 has to be in NFM for that...unless 'am mistaken.

[HafeezAkmal]

Thanks Anuj and Pandora. I used your query and it's working Anuj!

Thank you!

By the way, With ROW-ID, will there be an additional overhead, just thinking...