# 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).

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.