Find second Highest Salary for each department.

In previous mysql post, we found the second highest salary in employee table. In this problem we would like to find second highest salary for each department.

EMPLOYEE TABLE

EmpName Salary Department
Steve 150$ Finance
James 120$ IT
Andrew 120$ Finance
Mark 100$ Back-Office
Adam 110$ IT
Lewis 130$ Finance
Smith 125$ Back-Office

Solution : A good way to approach this problem is to break it down. First Calculate maximum salary in each department.

Select Department,Max(Salary) as Salary 
From Employee 
Group by Department.

Now find salaries excluding the maximum salary for each department.

Select E1.Department,E1.Salary
From Employee E1,(Select Department,Max(Salary) as Salary 
                  From Employee group by Department) E2
Where E1.Department = E2.Department 
And E1.Salary<E2.Salary

Treat the result of above sql as a table and Now find maximum salary for each department from this. Final Sql will be :

Select Department,Max(Salary)
From (Select E1.Department,E1.Salary 
      From Employee E1,(Select Department,Max(Salary) as Salary 
                        From Employee group by Department) E2
      Where E1.Department = E2.Department
      And E1.Salary<E2.Salary) E 
Group by Department

5 Thoughts on “Find second Highest Salary for each department.

  1. Rohit Khandelwal on September 29, 2013 at 4:07 pm said:

    Efficient solution using 2 sql only
    select max(salary),E1.Department from Employee E1,(Select max(salary) as sal,Department from Employee group by Department ) E2 where E1.salary<E2.sal and E1.Department = E2.Department group by E1.Department;

  2. Amit Arora on May 21, 2015 at 10:39 am said:

    TableName: EMP_MGR
    Emp_ID Emp_Name Salary Mgr_ID
    1 Aman 45000 NULL
    2 Deepak 35000 1
    3 Pankaj 32000 1
    4 Sapna 25000 1
    5 Rajan 50000 NULL
    6 Nupur 18000 5
    7 Anamika 18000 5
    8 Preet 22000 NULL
    9 Shalu 27000 NULL
    10 Jyoti 12000 9
    11 Omesh 25000 9
    12 Rakesh 21000 9

    SELECT MAX(salary),E1.mgr_id FROM EMP_MGR E1,
    (SELECT MAX(salary) AS sal,mgr_id FROM EMP_MGR GROUP BY mgr_id ) E2
    WHERE E1.salary<E2.sal and E1.mgr_id = E2.mgr_id GROUP BY E1.mgr_id;

    I want to find 2nd Highest record from each group including Managers(NULL group)
    Expected Result:
    Mgr_ID Salary
    NULL 45000
    1 32000
    5 18000
    9 15000

  3. Helpful

  4. Sudhir on January 27, 2016 at 6:02 am said:

    Select e1.deptno,e1.salary from employee e1 where (1) =
    (select count(e2.distinct salary) from employee e2 where e1.deptno=e2.deptno and e2.salary > e1.salary)

  5. The last query didn’t give me the correct result when I had 2 equal second highest salary. That is, there are 3 entries in the table for a dept where salaries are: 65000, 35000, 35000. When I ran the last query in the result set I got 2 entries for the same dept. So I modified a little:

    Select Distinct E1.Department, E1.Salary
    From [Emp1] E1,(Select Department,Max(Salary) as Salary
    From [Emp1]
    group by Department) E2
    Where E1.Department = E2.Department
    And E1.Salary<E2.Salary

Leave a Reply to Nitin Cancel 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