# 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
```

### 0 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. 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)