✍️Finding the Nth highest salary from an SQL table

Recently I came across an interesting problem in SQL. This is an interesting problem, even in Data Structures and Algorithms.

Let's not waste time and jump ahead.

So, here's the problem statement:

Finding the Nth highest salary from an SQL table

I wanted the documentation of the solution to be interactive. This can be achieved with the help of using an online SQL compiler. Use the online compiler to test out the code: https://www.programiz.com/sql/online-compiler/

Here are the steps we will take to solve this problem.

Create an employee table with salary and name as fields.
Insert data into the table to find the nth largest salary.
Write the query for finding the nth largest salary.

First, we need to create the table.

Here's the create table query

CREATE TABLE Employees (
    Salary int,
    Name varchar(255)
);

Insert data into the table to find the nth largest salary.

Insert values into Table

Insert into Employees (Salary, Name) values (500, 'E');
Insert into Employees (Salary, Name) values (200, 'B');
Insert into Employees (Salary, Name) values (400, 'D');
Insert into Employees (Salary, Name) values (600, 'F');
Insert into Employees (Salary, Name) values (700, 'G');
Insert into Employees (Salary, Name) values (800, 'H');
Insert into Employees (Salary, Name) values (300, 'C');
Insert into Employees (Salary, Name) values (100, 'A');

Writing the query for finding the nth largest salary.

Before we write the query for the nth largest salary, let's write the query for the maximum salary.

Find the maximum salary.

select max(salary) from employees;

Now, let's write the query to find the second maximum salary.

Find the second max salary.

select max(salary) from employees where salary < (select max(salary) from employees);

One major difference between the queries of maximum and second maximum salaries is the addition of the where clause in the second maximum salary query. In the second maximum salary query, we are making use of what is called a subquery to find the salary that is less than the maximum salary but greater than other salaries.

Now the idea to find the Nth maximum is to find the maximum salary less than the maximum N-1 elements.

We will make sure of the subquery again to get to the nth maximum.

But first, let's write the query for the top N-1 salaries.

select salary from employees order by salary desc limit N-1;

Now that we have top N-1 salaries, we can make use of not in operator to find the nth max salary

select max(salary) from employees where salary not in (select salary from employees order by salary desc limit N-1);

This gives us the nth maximum salary.

Can you think of any edge cases we missed while proposing this solution?

Take some time to think!

What we missed was handling the duplicate salaries.

Can you think of ways to use a subquery to make it get the nth maximum salary?

Hint: see if you can use ORDER BY DESC an ORDER BY ASC

Use of Offset keyword:

Finally, one easiest way to solve this problem is to use the OFFSET keyword.

OFFSET dictates the number of rows to skip from the beginning of the returned data before presenting the results.

So, what it means is

If we provide offset as 0, zero rows are skipped from the start.
If we provide offset as 1, one row is skipped, and results start from the 2nd row.
If we provide offset as n-1, n-1 rows are skipped, and results start from the nth row.

Can you think of a keyword that could help us here?

If you haven't guessed already, that's limit the keyword. We will limit the results to return only one row.

Here's the query for your reference:

select * from employees order by salary desc limit 1 offset N-1;

How would you solve this problem? Let me know that in the comments!

Share this article

Copy and share this article: https://www.narendravardi.com/nth-salary

Recommendations

❤️ Enjoyed this article?

Forward to a friend and let them know where they can subscribe (hint: it's here).

Anything else? Comment below to say hello, or drop an email!