select ranksala.dept_no, ranksala.emp_no, ranksala.salary
from
(
select
sala.dept_no,
sala.emp_no,
sala.salary,
row_number() over(partition by sala.dept_no order by sala.salary desc) as ASD
from
( select A.emp_no, A.dept_no, B.salary
from dept_emp as A
left join salaries as B
on A.emp_no = B.emp_no
) as sala
) as ranksala
where ranksala.ASD = 1
order by ranksala.dept_no