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:
- 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”)
- 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;
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;
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;
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;
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;
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.