Page 1 of 1
"N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 3:14 pm
by 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.
Re: "N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 3:33 pm
by zprogrammer
What is the DB2 version at your shop?
And what happened when you tried to execute this as a DB2 query?
Re: "N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 4:59 pm
by 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?
Re: "N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 5:11 pm
by 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.
Re: "N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 5:20 pm
by 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.
Re: "N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 5:50 pm
by zprogrammer
IIRC , You could generate the row id...
Re: "N"th maximum Salary and SQL Query.
Posted: Fri Apr 04, 2014 6:05 pm
by Anuj Dhawan
Right, and DB2 has to be in NFM for that...unless 'am mistaken.
Re: "N"th maximum Salary and SQL Query.
Posted: Mon Sep 29, 2014 4:32 pm
by 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...