Finding out the N’th highest salary from a table is one of the most frequently asked SQL interview questions. In this article, we will discuss four different approaches to find the Nth highest salary.
In the previous blog we learn how to find the second highest salary in SQL, now moving forward we will learn how to find the Nth highest salary. In this article, we will look into various approaches to tackle this problem statement in detail. We will query for the N’th Highest salary using the below approaches:
- 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 step by step approach how to find the nth highest salary in sql using different methods.
Using the LIMIT Clause
In this approach, we order the salary data in descending order. This leads to the highest salary being on the top of the query set followed by 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 below example 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 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 make use of 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 labeling 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 this article, we explored 4 different approaches to finding out the highest salary in a table. This is one of the most frequently asked SQL interview questions and we have managed to arrive at the solution with multiple approaches.
Download this article as PDF to read offline
Download as PDF
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
Trending Management Courses
Top Picks & New Arrivals
Comments