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

GO