Find second highest salary in employee table

Our problem is to find 2nd highest salary from the employee table using mysql .

EMPLOYEE TABLE
Steve    150$
James    120$
Andrew   120$
Mark    100$

First Method: We will sort the salaries in descending order and will limit the solution by one.

SELECT name, salary
FROM employees
ORDER BY salary DESC LIMIT 1 , 1

But this will not give all the results if there are two employees whose salaries are same(second highest).
Above solution will return Steve only.

So here is the alternative way using nested subquery:

SELECT name, salary
FROM EMPLOYEE
WHERE salary =
     (SELECT MAX(salary) FROM EMPLOYEE WHERE salary <
     (SELECT MAX(salary) FROM EMPLOYEE))

5 Thoughts on “Find second highest salary in employee table

  1. Pingback: Find second Highest Salary for each department | CrazyforCode

  2. Sumit Garg on August 30, 2013 at 10:07 pm said:

    SELECT name, max(salary)
    FROM EMPLOYEE
    WHERE salary < (SELECT MAX(salary) FROM EMPLOYEE)

    Better approach ..Using only one sub query..

  3. Lakhan Singh on August 16, 2014 at 1:45 am said:

    Sumit
    your Quiry gives wrong answer..

  4. Sajib on May 6, 2015 at 4:31 pm said:

    # SQL Query for the 2nd height salary holder:
    SELECT name, salary
    FROM EMPLOYEE
    WHERE salary < (SELECT MAX(salary) FROM EMPLOYEE) ORDER BY salary DESC LIMIT 1

  5. Sudhakar on June 16, 2015 at 12:49 pm said:

    student table with id, student_name, department_id, salary

    select department_id, min(salary) as Second_Highest_Per_Department, student_name from ( select department_id,student_name, salary from student where salary NOT IN ( select max( salary ) from student group by department_id ) ) as tab group by department_id

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation