Find the Nth highest employee salary from an Employee table in MySql

Find the Nth highest employee salary from an Employee table is the most common question asked in interviews. There are many ways to get Nth highest employee salary based upon which database you are using. In this article i am using MySQL database for demonstration.

Read Also : MySQL Query To Find The Second Highest Salary

Find the Nth highest employee salary

Let’s create a simple example of employees table. We will populate this table with id, name, salary and department of employees.

SELECT * FROM employees;
idnamesalarydepartment
1Tom4000Technology
2Sam6000Sales
3Bob3000Technology
4Alen8000Technology
5Jack12000Marketing
6Charlie8000Human Resources
7Harry6000Marketing
8Jacob4000Human Resources

Method 1 – Nth highest salary in MySQL using LIMIT clause:

SELECT DISTINCT(salary) FROM employees ORDER BY salary DESC LIMIT N-1, 1;

2nd highest salary in MySQL using LIMIT clause:

SELECT DISTINCT(salary) FROM employees ORDER BY salary DESC LIMIT 1,1;
salary
8000

3rd highest salary in MySQL using LIMIT clause:

SELECT DISTINCT(salary) FROM employees ORDER BY salary DESC LIMIT 2,1;
salary
6000

Method 2 – Nth highest salary in MySQL using SubQuery:

SELECT DISTINCT( salary ) 
FROM   employees Emp1 
WHERE  N = (SELECT Count(DISTINCT ( Emp2.salary )) 
                FROM   employees Emp2 
                WHERE  Emp2.salary >= Emp1.salary); 

Here, replace the N with any number. For example, if you want to find 5th highest salary , then replace N with 5 like below query –

SELECT DISTINCT( salary ) 
FROM   employees Emp1 
WHERE  5 = (SELECT Count(DISTINCT ( Emp2.salary )) 
            FROM   employees Emp2 
            WHERE  Emp2.salary >= Emp1.salary);
salary
3000

Now suppose based on the above table you want to get all employees have Nth highest salary with all details. For example, if you want to find all employees have 3rd highest salary with all details:

SELECT * 
FROM   employees Emp1 
WHERE  3 = (SELECT Count(DISTINCT ( Emp2.salary )) 
            FROM   employees Emp2 
            WHERE  Emp2.salary >= Emp1.salary);
idnamesalarydepartment
2Sam6000Sales
7Harry6000Marketing

Leave A Reply

Your email address will not be published.