nawe

SELECT e.DepartmentID, MAX(e.Salary) AS [ThirdHighestSalary]
FROM Employees AS e
WHERE e.Salary < (SELECT MAX(m.Salary)
                  FROM Employees AS m
                  WHERE e.DepartmentID = m.DepartmentID AND
                        m.Salary < (SELECT MAX(p.Salary)
                                    FROM Employees AS p
                                    WHERE m.DepartmentID = p.DepartmentID))
GROUP BY e.DepartmentID