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