How to Find Nth Highest Salary in SQL
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.
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:
- 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
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
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.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
