How to find nth highest salary in SQL Server using a Sub-Query

Create table Employees

(

     ID int primary key identity,

     Name nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

Insert into Employees values ('Ram', 'Male', 70000)

Insert into Employees values ('Vijay', 'Male', 60000)

Insert into Employees values ('Siva', 'Male', 45000)

Insert into Employees values ('Ram', 'Male', 70000)

Insert into Employees values ('Jeeva', 'Male', 45000)

Insert into Employees values ('Saranya', 'Female', 30000)

Insert into Employees values ('Priya', 'Female', 35000)

Insert into Employees values ('Abdul', 'Male', 80000)

To find the highest salary it is straight forward. We can simply use the Max() function as shown below.

Select Max(Salary) from Employees

To get the second highest salary use a sub query along with Max() function as shown below.

Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

To find nth highest salary using Sub-Query

SELECT TOP 1 SALARY

FROM (

      SELECT DISTINCT TOP N SALARY

      FROM EMPLOYEES

      ORDER BY SALARY DESC

      ) RESULT

ORDER BY SALARY

To find nth highest salary using Rank

WITH RESULT AS

(

    SELECT SALARY,

           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK

    FROM EMPLOYEES

)

SELECT TOP 1 SALARY

FROM RESULT