SQL Query to Find Nth Highest Salary

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

For this 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. Please refer to the SQL Derived Table article on the SQL tutorial page.

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: The top frequent query is returning the first record order by Ascending order. Check more SQL Interview questions.

SQL Query to Find the 4th Highest Salary Example

The following Query is to Find the fourth-highest salary. For this, we use the SQL ROW NUMBER concept to find the fourth highest salary. You can also use any of the available SQL Ranking Functions such as SQL RANK, SQL DENSE_RANK, or SQL 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 the 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.

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.