How to find nth highest salary in SQL?

It’s a comm interview question on how to find the second highest salary, second highest salary, or nth highest salary in SQL. In this article, we will provide four ways to achieve the solution. If you read through this article, you will answer these questions with multiple solutions to impress the interviewer.

I have created one table called Employee with three columns Id, Name, and Salary, We will be using the same table for all solutions.

SQL table,select second highest salary in SQL

As you can see in the above table, Employee with name D takes the second-highest salary among all.

Using Max() function in SQL

SQL Server MAX() function is an aggregate function that returns the maximum value in a set.

Select the salary column using SQL Server MAX() function.SELECT Max(salary) AS SalaryFROM employeeWHERE salary < (SELECT Max(salary)FROM employee)

Select salary column along with Id and Name

SELECT TOP 1 id,
NAME,
Max(salary)
FROM employee
WHERE salary < (SELECT Max(salary)
FROM employee)
GROUP BY id,
NAME,
salary
ORDER BY salary DESC

Using Inline SQL query

We can use an inline query to select the second-highest or nth highest salary in the table. You just have to replace the number in the inline query.

SELECT TOP 1 *
FROM   (SELECT DISTINCT TOP 2 salary
        FROM   employee
        ORDER  BY salary DESC) AS t
ORDER  BY t.salary ASC

You can just put 3 in the inline query, In case you want to select the 3rd highest salary.

Using Dense_Rank()

DENSE_RANK gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank
from Employee

In order to get the 2nd highest salary you just need to keep above query as an inline query and place the where condition with Rank=2

select top 1 Id,Name,Salary  from (
select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as R
from Employee) result
where result.R=2

Using CTE

CTE or common table expression is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

We are using the same Dense_Rank() query used in the previous solution. This time we will be using CTE instead of an inline query.

with tmp_emp as(
select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank
from Employee)

Above query will keep the result in the temp result set using CTE.

Now, we just need to use a tmp_emp table with where clause and number to get the nth highest salary.

select Id,Name,Salary from tmp_emp where tmp_emp.Rank=2

So, in this article, we found multiple ways to find the nth highest salary in SQL. This article can help you to answer the various interview questions like.

how to find the nth highest salary in SQL using a rank() function.
nth highest salary in SQL using dense_rank
nth highest salary in SQL using CTE

If you know any other solution apart from these, don’t forget to provide in the below comment section.

About Me:-
I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us here if you are interested to evaluate if Looker is right for you or any other BI solution.

How to find nth highest salary in SQL?