Get top salary by department without using any subquery

Question

Let's say we have 2 database tables - emp and dept, which consists of the following columns

emp: empid, deptid, salary

dept: deptid, deptname

The deptid column in emp can be joined with deptid column in dept column. Note that some departments don't have any employee. For those cases, the deptid in dept table won't exist in emp table. We need to find top salary in each department. For departments that don't have any employee, we need to assign them the highest salary from emp table. One requirement is that we can NOT use subquery, but CTE (common table expression) is allowed.

Below is the query I built:

with cte as 
(Select d.deptid, e.salary, row_number() over (partition by d.deptid order by e.salary desc) as rnk,
row_number() over(order by e.salary desc) as salary_rank    
from emp e 
join dept d on e.deptid = dept.deptid),

top_salary as 
(Select d.deptid, e.salary 
from emp e 
join dept d on e.deptid = dept.deptid
order by e.salary desc
limit 1)


(Select d.deptid, cte.salary 
from cte 
join dept d on d.deptid = cte.deptid
where cte.rnk = 1) as t1

UNION 

(Select d.deptid, ts.salary  
from dept d 
left join cte on cte.deptid = d.deptid 
left join top_salary ts on ts.deptid = cte.deptid 
where cte.salary is null
)

But I am not sure if I did it correctly, especially in cases where the departments don't have any employees. I am also not sure if the 2 queries I wrote surrounding the UNION clause are considered subqueries. If they are indeed subqueries, then is there a way I can rewrite that query without using any subquery?

Answer 1

We need to find top salary in each department. For departments that don't have any employee, we need to assign them the highest salary from emp table.

Your attempt seems overly complicated:

with edmax as (
      select e.deptid, max(e.salary) as max_salary
      from emp
      group by e.deptid
     ),
     emax as (
      select max(e.salary) as max_salary
     )
select d.*, max(edmax.max_salary, emax.max_salary) as max_salary
from dept d left join
     edmax
     on d.deptid = edmax.deptid cross join
     emax;

The basic idea is to calculate the maximum salary for each department and then "default" to the overall maximum.

By the way, you could do this only with joins:

select d.deptid, d.name,
       coalesce(max(de.salary), max(d.salary))
from emp e cross join
     dept d left join
     dept de
     on de.deptid = e.deptid
group by d.deptid, d.name;

I don't recommend this approach but you might want to understand it.