Database Challenges

--Paul_Hutagalung_TBC_1.sql
with a2 as ( -- get maximum salary
select department, max(salary) salary
from @employees
group by department
)
, a3 as ( -- eliminate maximum salary
select department, salary
from @employees a
where not exists (
	select 1 from a2 where a.department = a2.department and a.salary = a2.salary)
group by department, salary
)
, a4 as ( -- get another maximum salary
select department, max(salary) salary
from a3
group by department
)
select b.* -- retrieve all data based on previous result
from @employees b
inner join a4
on b.department = a4.department
and a4.salary = b.salary
order by b.salary

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.