Admin
In this SQL tutorial, we are about to see how to find Nth highest salary in MySQL with the help of various examples. To achieve this, we will explore multiple ways to find the Nth highest salary and see the Nth highest salary in global data and the Nth highest salary by department.
Let’s get started.
Headings of Contents
Find Nth Highest Salary in MySQL
To find the Nth Highest salary, You can use various techniques in SQL, Here I will demonstrate methods for finding the highest salary using Standard SQL and this SQL query will work with almost all the common databases like MySQL, SQL Server, PostgreSQL, etc. Here, I am about to use MySQL Database.
First will find the Nth highest salary globally using various techniques.
For a demonstration of this article, I have created two tables in my MySQL database called employees and department as you can see below.
Table Name – employees
Table Name – department
Find Nth Highest Salary Globally
1. Using LIMIT and OFFSET Clause.
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET n-1;
Replace n with your desired rank.
To understand this, Let me display records without using LIMIT and OFFSET. The below query will display all the records from the employees table and records will be ordered by salary in descending order.
SELECT * FROM employees e ORDER BY e.salary DESC;
After executing the above query, I got the below result set. For example, I want to get 3rd highest salary in the below result set. As we know the third-highest salary is 47500 therefore to achieve 3rd highest salary first we have to hide the first two rows and display only the third row.
To hide the first two rows we will use OFFSET and to show the third row we will see the LIMIT clause, let’s see how.
SELECT e.salary FROM employees e ORDER BY e.salary DESC LIMIT 1 OFFSET 2;
After executing the above SQL query the output will be 47500 because it is the 3rd highest salary in my case, it might be different in your case. If you want to get more columns then you can pass column names separated by commas.
SELECT first_name,
last_name,
salary
FROM employees e
ORDER BY e.salary DESC
LIMIT 1
OFFSET 2;
Output
| first_name | last_name | salary |
|---|---|---|
| Sandeep | Singh | 47500 |
This is how you can use LIMIT and OFFSET to get Nth highest salary.
2. Using ROW_NUMBER() OR DENSE_RANK() Window Function
The ROW_NUMBER() and DESNE_RANK() are window functions in MySQL that are used to provide a rank to each record within a group or window.
You can learn all about the window from our MySQL window function tutorial.
Let’s use the ROW_NUMBER() window function to find the Nth highest salary.
SELECT first_name,
last_name,
salary,
ROW_NUMBER() over(ORDER BY salary DESC) AS 'salary_rank'
FROM employees;
The output of the above query will be:
As you can see in the above result set, the third employee got 3rd rank because that employee has the third highest salary. To display only that employee we will use the above query as a subquery.
SELECT first_name,
last_name,
salary
FROM
(SELECT first_name,
last_name,
salary,
ROW_NUMBER() over(ORDER BY salary DESC) AS 'salary_rank'
FROM employees) AS nth_salaried_employee
WHERE salary_rank = 3;
The final output would be:
| first_name | last_name | salary |
|---|---|---|
| Sandeep | Singh | 47500 |
3. Using MySQL CTE ( Common Table Expression ):
CTE stands for Common Table Expression. It is a temporary result set that can be used within SELECT, INSERT, UPDATE, and DELETE statements.
CTE in MySQL is always defined with the WITH keyword.
Let’s use the CTE to get Nth highest salary.
WITH nth_salaried_employee
AS (SELECT first_name,
last_name,
salary,
ROW_NUMBER()
OVER(
ORDER BY salary DESC) AS 'salary_rank'
FROM employees
)
SELECT *
FROM nth_salaried_employee
WHERE salary_rank = 3;
After executing the above query the output will be:
| first_name | last_name | salary |
|---|---|---|
| Sandeep | Singh | 47500 |
In the above query, nth_salaried_employee represents the CTE ( Common Table Expression ) name.
So far we have seen how to find the Nth highest salary globally, Now it’s time to get the Nth highest salary within each department.
Find Nth Highest Salary By Department in MySQL
In all the above examples, we have seen the process of getting Nth highest salary in whole data but sometimes we might have a requirement to get Nth highest salary by department wise.
Here we are about to use ROW_NUMBER() and CTE to achieve Nth highest salary department-wise. Here, I am about to get the 2nd highest salary by department.
1. Using ROW_NUMBER()
SELECT first_name,
last_name,
salary,
department_name
FROM (SELECT first_name,
last_name,
salary,
d.department_name,
ROW_NUMBER()
OVER(
partition BY department_name
ORDER BY salary DESC) AS 'salary_rank'
FROM employees e
INNER JOIN department d
ON d.id = e.dep_id) AS nth_salaried_employee
WHERE salary_rank = 2;
The Output will be:
| first_name | last_name | salary | department |
|---|---|---|---|
| Vaibhav | Pathak | 32000 | Account |
| Diksha | Rao | 42000 | Digital Marketing |
| Harshita | Kumari | 37000 | HR |
| Vishvajit | Rao | 40000 | IT |
Let’s break down the above query.
In the above query, two parts exist, Parent query and Subquery.
The below query is used as a subquery in the parent query.
SELECT first_name,
last_name,
salary,
d.department_name,
ROW_NUMBER()
OVER(
partition BY department_name
ORDER BY salary DESC) AS 'salary_rank'
FROM employees e
INNER JOIN department d
ON d.id = e.dep_id
And this query is using a parent query.
SELECT first_name,
last_name,
salary,
department_name
FROM (....) AS nth_salaried_employee
WHERE salary_rank = 2
This is how you can use ROW_NUMBER() to find the Nth highest salary by department.
Let’s use the MySQL CTE to achieve the same result.
2. Using CTE ( Common Table Expression )
There are no major changes in CTE, just use the above sub-query part as CTE.
WITH nth_salaried_employee
AS (SELECT first_name,
last_name,
salary,
d.department_name,
ROW_NUMBER()
OVER(PARTITION BY department_name
ORDER BY salary DESC) AS 'salary_rank'
FROM employees e
INNER JOIN department d
ON d.id = e.dep_id)
SELECT *
FROM nth_salaried_employee
WHERE salary_rank = 2;
The Output will be:
| first_name | last_name | salary | department |
|---|---|---|---|
| Vaibhav | Pathak | 32000 | Account |
| Diksha | Rao | 42000 | Digital Marketing |
| Harshita | Kumari | 37000 | HR |
| Vishvajit | Rao | 40000 | IT |
This is how you get Nth highest salary globally and department-wise.
See Also:
- How to check the length of strings
- How to concatenate multiple columns
- How to show columns of a table
- How to concatenate strings in MySQL
- How to add new columns in the Existing table
- How to import a CSV file in MySQL
- How to extract the year from the date in MySQL
- How to extract month from date in MySQL
- How to handle Null values in MySQL
- How to get the last day of the month in MySQL
Conclusion
Throughout this article, we have seen how to find Nth highest salary globally and department-wise. This is one of the most important questions from the interview point of view. That’s why as a developer, Data Engineers, and Data analysts we must know how to get Nth highest salary in the whole data or by the department.
If you like this article, please share and keep sharing and visiting for further SQL tutorials.
Happy Coding…
