*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 n ^{th} 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 n^{th} highest salary with precision and ease.*

- Using the LIMIT clause
- Using DENSE_RANK() function
- Using ROW_NUMBER() function
- Using RANK() function

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);

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);

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

SELECT * FROM employee;

**Output**

**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;

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;

**Output:**

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_queryWHERE alias_for_nested_query=N;

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 kWHERE ranking=3;

**Output:**

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;

**Output:**

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 kWHERE ranking=3;

**Output:**

**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;

**Output:**

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 kWHERE ranking=3;

**Output:**

**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