MySQL query to find the second highest salary

Write a MySQL query to find the second highest salary from the employees table. There are many ways to get second highest salary based upon which database you are using as different database provides different feature which can be used to find the second highest salary from the employees table.

Read Also : Find The Nth Highest Employee Salary From An Employee Table

MySQL Query To Find The Second Maximum Salary

Consider an employees table, which is having the following records −

SELECT * FROM employees;
idnamesalarydepartment
1Tom4000Technology
2Sam6000Sales
3Bob3000Technology
4Alen8000Technology
5Jack12000Marketing

For example, in above table, “Alen” has the second maximum salary as 8000.

Second maximum salary using sub query and IN clause

SELECT MAX(salary) FROM employees WHERE salary NOT IN ( SELECT Max(salary) FROM employees);
MAX(salary)
8000

Using sub query and < operator instead of IN clause

SELECT MAX(salary) From employees WHERE salary < ( SELECT Max(salary) FROM employees);
MAX(salary)
8000

Using the LIMIT clause in query

SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;
salary
8000
6 Comments
  1. Jaykumar Patil says

    What if two employees have same second highest salary and I also want to fetch other details like name, technology ?

    1. Full Stack developer says

      That MySQL query will return all employees have same second highest salary with all employee details

      SELECT * 
      FROM   employees 
      WHERE  salary IN (SELECT Max(salary) 
                        FROM   employees 
                        WHERE  salary NOT IN (SELECT Max(salary) 
                                              FROM   employees)); 
      
  2. Kamal Nayan says

    What will you do for nth highest (say 10th)? Don’t use LIMIT.

    1. Full Stack developer says

      Use below MySql Query to find Nth highest employee salary from an employees table

      SELECT *
      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 have to find 10th highest salary , then replace n with 10 like below query –

      SELECT *
      FROM employees Emp1
      WHERE 10 = (
                      SELECT COUNT( DISTINCT ( Emp2.salary ) )
                      FROM employees Emp2
                      WHERE Emp2.salary >= Emp1.salary
                  );
      
  3. Rajesh says

    How about

    SELECT * FROM employees ORDER BY salary DESC limit 1,1;

    1. Full Stack developer says

      you are right. thanks

Leave A Reply

Your email address will not be published.