SQL Query to Find Nth Highest Salary

Write a SQL Query to find Nth highest salary, 2nd highest salary, or third highest salary is the most common interview question. In this article we will show you the best possible way to write SQL Server query to find nth highest salary with example. For this Nth Highest Salary demo, we use the Employee Details table.

SQL Query to Find Nth Highest Salary 1

SQL Query to Find Nth Highest Salary Example 1

In this SQL Server example we are going to use the Derived Table concept to find the third highest salary.

-- SQL Query to find 3rd Highest Salary 
USE [SQLTEST]
GO

SELECT TOP 1 [FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome] AS 'Highest Salary'
      ,[Sales]
FROM (
        SELECT DISTINCT TOP 3 [FirstName]
			     ,[LastName]
			     ,[Education]
			     ,[YearlyIncome]
			     ,[Sales]
	FROM [EmployeesDetails] 
	ORDER BY [YearlyIncome] DESC
      ) EMP 
ORDER BY [YearlyIncome] ASC

Query inside the From clause is selecting top three employees order by salary in descending order. I suggest you to refer Top Clause, and Order by. If you want 2nd highest then replace 3 with 2 in Top clause

SQL Query to Find Nth Highest Salary 2

MAIN OUTPUT
And the Top frequent query is returning the first record order by Ascending order.

SQL Query to Find Nth Highest Salary 3

SQL Query to Find 4th Highest Salary Example 2

We use the ROW NUMBER concept to find the fourth highest salary. You can also use any Rank Function such as: Rank, Dense Rank, Ntile to get the same result.

-- SQL Query to find 4th Highest Salary 
USE [SQLTEST]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[ROW NUMBER] AS 'Highest Salary'
      ,[Sales]
FROM (
        SELECT [FirstName]
	      ,[LastName]
	      ,[Education]
	      ,[YearlyIncome]
	      ,ROW_NUMBER() OVER (
		                   ORDER BY [YearlyIncome] DESC
		                  ) AS [ROW NUMBER]
	      ,[Sales]
	FROM [EmployeesDetails] 
      ) EMP 
WHERE [ROW NUMBER] = 4

As you can see Row_Number() in inner query is giving Ranks based on their yearly Income

SQL Query to Find Nth Highest Salary 4

We are selecting the required rank from that inner query. If you want 2nd highest then replace 4 with 2 in where clause.

SQL Query to Find Nth Highest Salary 5

You can also use SQL CTE to get the same result.

SQL Query to Find Nth Highest Salary in each Group

We find the second highest salary in each group. This is the same query that we used in our previous example but we added the PARTITION BY Clause to separate the groups.

-- SQL Query to find 3rd Highest Salary 
USE [SQLTEST]
GO
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[ROW NUMBER] AS 'Highest Salary'
      ,[Sales]
FROM (
        SELECT [FirstName]
	      ,[LastName]
	      ,[Education]
	      ,[YearlyIncome]
	      ,ROW_NUMBER() OVER (
		                   PARTITION BY [Education]
                                   ORDER BY [YearlyIncome] DESC
		                  ) AS [ROW NUMBER]
	      ,[Sales]
	FROM [EmployeesDetails] 
      ) EMP 
WHERE [ROW NUMBER] = 2

As you can see Row_Number() in inner query is giving Ranks for each partition (Education) based on their yearly Income

SQL Query to Find Nth Highest Salary 6

We are selecting the required rank from that inner query. Again, If you want 3nd highest then replace 2 with 3 in where clause.

SQL Query to Find Nth Highest Salary 7

Comments are closed.