DB: SQL SERVER - Find Nth Highest Salary of Employee

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,

SELECT TOP               1 salary 
                 

FROM               (
                

SELECT DISTINCT TOP               6 salary 
                 

FROM               employee
                 

ORDER BY               salary               DESC              )               a 
                 

ORDER BY               salary           

You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP               1 salary 
                 

FROM               (
                

SELECT DISTINCT TOP               n salary 
                 

FROM               employee
                 

ORDER BY               salary               DESC              )               a 
                 

ORDER BY               salary           

where n > 1 (n is always greater than one)

Same example converted in SQL Server 2005 to work with Database AdventureWorks.

USE               AdventureWorks              ;
                

GO
      
           

SELECT TOP               1 Rate
                 

FROM               (    
                

SELECT DISTINCT TOP               4 Rate
                 

FROM               HumanResources.EmployeePayHistory
                 

ORDER BY               Rate               DESC              )               A
                 

ORDER BY               Rate