Reputation: 541
For Finding nth highest salary I am using
select salary from
(
select distinct ROW_NUMBER() over (order by salary desc) as rownum,Salary
from Employee
)a
where rownum=2
However if i have same salary like
70000
70000
60000
50000
50000
When executing the query i am getting second highest salary
70000 instead 60000
how to avoid duplicates?
Upvotes: 0
Views: 1970
Reputation: 9
Here is the finding nth highest Using CTE(Common Table Expression)
With Result as
(
select salary ,DENSE_RANK () over(ORDER BY salary DESC) AS Denserank FROM employees
)
select Top 1 salary
FROM Result
where Result. Denserank=2
Upvotes: 0
Reputation: 239824
Although Mark has already provided one answer, I'd say that you're using the wrong function. You don't want the row number, you want the RANK. And based on how you want to handle duplicates, specifically you should be using DENSE_RANK.
E.g.:
SELECT salary FROM
(
SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS rank, salary
FROM Employee
) T2
WHERE rank=2
Upvotes: 3
Reputation: 839254
It's the salaries that must be distinct, not the row numbers:
SELECT salary FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rownum, salary
FROM (SELECT DISTINCT Salary FROM Employee) T1
) T2
WHERE rownum=2
Upvotes: 3