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;

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;

3rd highest salary in MySQL using LIMIT clause:

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

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);

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);