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
Steve150$Finance
James120$IT
Andrew120$Finance
Mark100$Back-Office
Adam110$IT
Lewis130$Finance
Smith125$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

4 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. 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)

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