How to Find Nth Highest Salary in SQL

How to Find Nth Highest Salary in SQL

5 mins read39.2K Views Comment
clickHere
Updated on Jan 11, 2024 17:17 IST

Extracting valuable insights from large SQL databases is crucial in the world of data. One of the most common queries is finding the nth highest salary, which is essential for HR professionals, data analysts, and researchers. This guide offers a comprehensive approach to querying the nth highest salary by using SQL techniques. We cover efficient methods to identify the appropriate salary column, handle duplicate salaries, and customise the query for specific scenarios. Whether you're a beginner or an experienced SQL user, this guide will help you retrieve the nth highest salary with precision and ease.

How to find the nth highest salary in sql

In our previous blog post, we learned how to find the second-highest salary in SQL. We will discuss how to find the Nth highest salary using different approaches. This article will cover various methods to solve this problem statement in detail. We will use the following techniques to query for the Nth highest salary:

Before we start, let’s create a simple table of employees with their names and the corresponding salary using the below SQL statement:


 
CREATE TABLE employee (
emp_name TEXT NOT NULL,
salary INT NOT NULL
);
Copy code

Now let’s insert some data to the table using the below SQL commands:


 
-- insert data into table
INSERT INTO employee VALUES ('Tom',1500000);
INSERT INTO employee VALUES ('Dick',3900000);
INSERT INTO employee VALUES ('Hary',7700000);
INSERT INTO employee VALUES ('Mike',15000000);
INSERT INTO employee VALUES ('Harvey',33300000);
INSERT INTO employee VALUES ('Brush',2500000);
Copy code

Now, let’s check the current data inside our employee table using the below command:


 
SELECT * FROM employee;
Copy code

Output

2022_08_image-69.jpg

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

Now we will see a step-by-step approach to finding the nth highest salary in SQL using different methods.

Enrol now in leading online courses from top colleges to explore the world of SQL and databases. Advance your career in database management.

Using the LIMIT Clause

In this approach, we order the salary data in descending order. This leads to the highest salary being at the top of the query set and subsequently lower salaries. So the idea is to skip N rows from the top and get the first value that we get after skipping.

It is also important to note that the LIMIT clause takes in 2 components. The first component refers to the number of rows that we need to skip from the top. The second component refers to the number of rows that is to be displayed.

The SQL query for this approach would look like below:


 
SELECT emp_name AS Employee, salary AS Salary FROM employee ORDER BY salary DESC LIMIT N-1,1;
Copy code

Take a look at the example below for reference.

Example:

Here we will look for the 3rd highest salary in the employee table.


 
SELECT emp_name AS Employee, salary AS Salary FROM employee ORDER BY salary DESC LIMIT 2,1;
Copy code

Output:

2022_08_image-162.jpg

As we can see in the output, Harry has the 3rd highest salary after Harvey and Mike.

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Using DENSE_RANK() function

The DENSE_RANK() function is used to compute the rank of a row within an ordered group of rows. It returns the rank of the row specified. The ranking in the DENSE_RANK() function is integer values starting from 1.

So for our problem statement, the SQL query would look like the below:


 
SELECT * FROM(
SELECT emp_name, salary, DENSE_RANK()
over(ORDER BY salary desc) As alias_for_nested_query FROM employee) As alias_for_first_query
WHERE alias_for_nested_query=N;
Copy code

In the above query, substitute the value of N with the rank of salary needed by us. For instance, replace N with 2 to get the 2nd highest salary in the table.

Take a look at the below example for reference.

Example:

Here, we will query for the 3rd highest salary in the employee table.


 
SELECT * FROM(
SELECT emp_name, salary, DENSE_RANK()
over(ORDER BY salary DESC) AS ranking FROM employee) AS k
WHERE ranking=3;
Copy code

Output:

2022_08_image-163.jpg

As we can see in the output, Harry has the 3rd highest salary after Harvey and Mike.

Must Read: Subqueries in SQL

Must Read: SQL CREATE TABLE

Using ROW_NUMBER() function

We can also use the ROW_NUMBER() function to get the Nth highest salary from the employee table.  The ROW_NUMBER() window function returns the sequence numbers of the dataset. This approach involves ordering the dataset in descending order and labelling each one of them to a row number.

Example:

Here we will query for a dataset with the ranking of each user based on their salary.


 
SELECT emp_name, salary, ROW_NUMBER()
OVER(ORDER BY salary DESC) AS Nth_highest_salary
FROM employee;
Copy code

Output:

2022_08_image-164.jpg
 

As you can see in the above output, each row in the datasets has been assigned a rank based on its salary. To get the specific row( i.e., the row with the 3rd highest salary), we can make use of the following query with the ROW_NUMBER() function:


 
SELECT * FROM(
SELECT emp_name, salary, ROW_NUMBER()
over(ORDER BY salary DESC) AS ranking FROM employee) AS k
WHERE ranking=3;
Copy code

Output:

2022_08_image-165.jpg

Must Read: SQL WHERE

Must Read: Introduction to Normalization

Using the RANK() function

Similar to the ROW_NUMBER() function, the RANK() function can also be used to rank each row of the data set based on a specific column value(ie, salary in our case). This approach also involves ordering the datasets in descending order and labeling each one of them to a row number.

Take a look at the below example for reference.

Example:


 
SELECT emp_name, salary, RANK()
OVER(ORDER BY salary DESC) AS Nth_highest_salary
FROM employee;
Copy code

Output:

2022_08_image-166.jpg

To get the specific row( i.e., the row with the 3rd highest salary), we can make use of the following query with the RANK() function:


 
SELECT * FROM(
SELECT emp_name, salary, RANK()
over(ORDER BY salary DESC) AS ranking FROM employee) AS k
WHERE ranking=3;
Copy code

Output:

2022_08_image-167.jpg

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

Conclusion:

In conclusion, this article has provided valuable insights about finding the nth highest salary using SQL. With the help of the four different approaches discussed in this article, you can easily answer one of the most frequently asked SQL interview questions. These techniques can also be applied to find other nth highest values in a table. By mastering these SQL techniques, you can improve your chances of success in SQL interviews and become a proficient SQL developer. Remember to practice and apply these approaches in real-world scenarios to gain more experience.

FAQs on How to Find the nth Highest Salary in SQL

What is a common method to find the Nth highest salary in SQL?

A popular method is using the ROW_NUMBER() window function. This function assigns a unique number to each row within a partition of a result set, starting at 1 for the first row in each partition. By ordering the data by salary in descending order and partitioning it by department (if needed), you can easily select the Nth highest salary​.

How can I find the Nth highest salary using the TOP keyword in SQL Server?

In SQL Server, the TOP keyword can be effective for this purpose. The idea is to select the top N salaries in descending order and then choose the top salary from this subset. This method is often faster than using a subquery because it avoids the need to calculate the Nth maximum salary with a subquery​.

Is there a way to handle duplicate salaries while finding the Nth highest salary?

Yes, using the DENSE_RANK() function can be helpful in this scenario. This function assigns ranks to rows within a partition with no gaps, meaning duplicate values will receive the same rank. This ensures that if two or more employees have the same salary, they are treated correctly in the ranking​.

Can I use a self-join to find the Nth highest salary?

A self-join is another approach to determine the Nth highest salary. By joining the table to itself on the salary column, you can use a WHERE clause to filter out the required Nth highest salary.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments