2

I was trying to get nth highest salary from table using subquery and dense_rank method. below are the results for each method.

Subquery Method:

select Top 1 Salary 
from (
    select top 7 Salary from Employee order by Salary desc
) as Result
order by Salary

This method returns 7th highest salary as: 3000

Dense_Rank Method:

select * 
from (
    select Salary, DENSE_RANK() Over(order by Salary desc) DRank 
    from Employee
) as Result 
where DRank=7

This method returns 7th highest salary as: 1000

Initial Table data:

Employee Table

Can anyone give me an idea about which method is correct and why?

Kashinath Raut
  • 58
  • 6
  • `Top N` counts duplicates as separate values unlike `DENSE_RANK`. Use `ROW_NUMBER` instead. – Akina Jan 09 '20 at 16:30

1 Answers1

2

DENSE_RANK() does not do what you want. It assigns incremental numbers while giving the same rank to ties. So there could be more than 6 records with a higher salary than the records ranked 7th.

The other query gives you the correct result, but could be simplified by using the OFFSET/FETCH syntax, which is available in SQL Server since version 2012:

select * from employee order by salary offset 6 rows fetch next 1 row only
GMB
  • 216,147
  • 25
  • 84
  • 135