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 an SQL Server query to find nth highest salary with an example.

For this SQL Query to Find the Nth Highest Salary demo, we use the Employee Details table.

SQL Query to Find Nth Highest Salary Example

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 

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 the top three employees’ order by salary in descending order. I suggest you refer to Top Clause and Order by. If you want 2nd highest, then replace 3 with 2 in the Top clause

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

SQL Query to Find the 4th Highest Salary Example

The following SQL Query to Find the fourth Highest Salary. For this, we use the ROW NUMBER concept to find the fourth highest salary. You can also use any Rank Function such as Rank, Dense Rank, and Ntile to get the same result.

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 the inner query is giving Ranks based on their yearly Income

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

You can also use 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.

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 the inner query is giving Ranks for each partition (Education) based on their yearly Income

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