Select Find Nth Highest Salary Record in SQL Server

Find or Select nth highest salary record in ms sql

This is most frequentky asked question how to select or get nth highest record or nth row/record from any column of sql table.

for example select get or fetch 2nd (second highest) or nth highest salary of employee or 10th highest record from the table.

There are various ways to achieve this result, i've mentioned few here.













I have created Employee table with following schema.

1st method

To select 2nd highest salary or record we can use following query.

SELECT TOP 1 [Salary]
FROM 
(
SELECT  DISTINCT TOP 2 [Salary]
FROM [dbo].[Employee]
ORDER BY [Salary] DESC
) temp
ORDER BY [Salary] 

2nd method

To select 3rd highest salary or record we can use following query.

SELECT TOP 1 [Salary]
FROM ( SELECT  TOP 3 [Salary]
  FROM [dbo].[Employee] e1 GROUP BY e1.Salary
  ORDER BY [e1].[Salary] DESC) e2
  ORDER BY [Salary]

These queries holds good untill we are selecting only salary column and fails when we want to select all the columns or few more columns with salary as salary can be same for more then one employees or records.

For example if we change the first query to select 2nd highest salary with all the columns of table, output would be undesirable as shown below.

SELECT TOP 1 [Salary],[EmployeeName]
FROM 
(
SELECT  DISTINCT TOP 2 [Salary], [EmployeeName]
FROM [dbo].[Employee]
ORDER BY [Salary] DESC
) temp
ORDER BY [Salary]

To select all columns we can use queries mentioned below.

This query will give 4th highest salary record but will show only 1 highest record if even if there are multiple duplicate salary records.

SELECT TOP 1 * FROM [dbo].[Employee]
WHERE [Salary] NOT IN  
( 
  SELECT DISTINCT TOP 3 [Salary] FROM [dbo].[Employee]
  ORDER BY [Salary] DESC
)
ORDER BY [Salary] DESC

These 2 queries will select 4th highest salary with duplicate records.

SELECT * FROM [dbo].[Employee]
WHERE [Salary] = 
( 
  SELECT MAX([Salary]) FROM [dbo].[Employee] 
  WHERE [Salary] NOT IN
    ( 
      SELECT DISTINCT TOP (4-1) [Salary] FROM [dbo].[Employee] e1
      ORDER BY [Salary] DESC 
    )
)

SELECT *
FROM Employee E1
WHERE (4-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

We can also use sql ranking function to get desired result as follows.

SELECT * FROM 
(
  SELECT DENSE_RANK() OVER(ORDER BY [Salary] DESC)AS RowId, * 
  FROM [dbo].[Employee] 
) AS e1
  WHERE e1.RowId = 4  

Thanks

Suneel Kumar