Oracle Challenge #3 – Solution

This is the solution for Oracle Challenge #3. If you haven’t read the challenge, go and check it before you read the solution here.

So, the challenge was to write different SQL statements to return the list of employees that earn the most in their department.

As I said, there are 2 assumptions here, we can overcome them in most of the solutions quite easily. But for simplicity I just assume these two things:

1. There are no employees without departments (while hr.employees has, so in some queries you will see that I specifically added “where department_id is not null”)
2. There are no 2 people that earn the most in their departments. So for each department I should only see one person

I got quite a few comments on this post, including lots of interesting different ways to solve this. Like Iudith Mentzel‘s way with listagg and regex, or Oren Nakdimon‘s way with pattern matching. Others contributed a lot as well, like Jared Still, Diana Robete, zhwsh, and Chris Saxon (Chris replied on Twitter).

Thanks everyone, and if you have more ways, please add them as comments to either post.

In this post I’ll show my solutions. They are not too fancy or too complex, but only 2 of my solutions (the first 2 here) were included in the comments I got, no one suggested the other 3. Also note that I didn’t consider performance implications, so some solutions will perform better than others.

Subquery with composite predicate

select
   department_id,
   employee_id,
   salary
from employees
where (department_id,salary) in
      (select
          department_id,
          max(salary) s
       from employees
       where department_id is not null
       group by department_id)
order by department_id;

Analytic function

select department_id,employee_id,salary
from
   (select employee_id,
           department_id,
           salary,
           dense_rank() over (partition by department_id order by salary desc) r
    from employees where department_id is not null
)
where r=1
order by department_id;

Self join

select
   e.department_id,
   e.employee_id,
   e.salary
from
   (select
       department_id,
       max(salary) s
    from employees
    group by department_id) max_sal,
employees e
where
   e.salary=max_sal.s and
   e.department_id=max_sal.department_id
order by department_id;

Scalar subquery

select
   department_id,
   (select employee_id
    from employees
    where
       department_id=e.department_id and
       salary=e.s) employee_id,
   s
from
   (select department_id,max(salary) s
    from employees
    where department_id is not null
    group by department_id) e
order by department_id;

Correlated subquery

select
   department_id,
   employee_id,
   salary
from employees e
where salary=(select max(salary)
              from employees
              where department_id=e.department_id)
 order by department_id;

Summary

This was an interesting post, and as I said I got quite a few responses. It seems that people thought of many ways to solve it that are completely different than mine. As Chris said on Twitter, it’s also quite debatable what a “different approach” is, but either way, it was interesting and I learned quite a lot from you.