How to Find Nth Highest Salary in MySQL

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.

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.

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…