CODEWITHSUNDEEP
sql-server-2005t-sql
Gopi
Gopi

Reputation: 541

SQL Server 2005/2008 Finding nth highest salary

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

Answers (3)

Bharath.M
Bharath.M

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

Damien_The_Unbeliever
Damien_The_Unbeliever

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

Mark Byers
Mark Byers

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

Related Questions