Home > Interview Questions > SQL Server > How to find nth highest salary in sql

How to find nth highest salary in sql

by RAVINDRA   on 19/08/2014   Category: SQL Server   |  Level: Intermediate   |  Views: 1916    |  Points: 25     |  Starter 


Let's us Consider Employees table it contains Columns Namely: ID,FIRSTNAME,LASTNAME,GENDER,SALARY

To find nth highest salary 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 DENSERANK = N
------------------------------------------------------------------------------

To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.

Similarly, to find 3rd highest salary, simple replace N with 3.


« Can an abstract class have a constructor? If so what is the use?
» Is it possible to nest the UpdatePanel control in asp.net?
Post Question  |  Question Home

Recent Posts

User Responses


  Re :How to find nth highest salary in sql   
Posted by SSK
on 28/04/2016
Points : 5

There is also one more way to find all the employee having nth highest salary:
select * from employee e1 where (Select count(*) from employee e2 where e2.salary>=e1.salary)=N

replace N with number you want like if you need to find 3rd highest then replace N with 3

Submit feedback about this code snippet

Please sign in to post feedback

Latest Posts