MAX and GROUP BY - SQL

Question

I'm working on Oracle SQL and HR database, I'm trying to select maximum salary in department like this:

SELECT MAX(salary), department_id
FROM employees GROUP BY department_id;

It works fine, but I want to know >who< is earning the most, so I simply change query this way:

SELECT first_name, last_name, MAX(salary), department_id
FROM employees GROUP BY department_id;

And it's wrong. Could you help me, please?

Answer 1

The most efficient way to do this sort of analysis is generally to use analytic functions (window functions). Something like

SELECT first_name,
       last_name,
       salary,
       department_id
  FROM (SELECT e.*,
               rank() over (partition by department_id 
                                order by salary desc) rnk
          FROM employees e)
 WHERE rnk = 1

Depending on how you want to handle ties (if two people in the department are tied for the maximum salary, for example, do you want both people returned or do you want to return one of the two arbitrarily), you may want to use the row_number or dense_rank functions rather than rank.

Answer 2

Create a view:

create view max_salary as select max(salary), department_id from    employees group by department_id

Then create a query:

select first_name, last_name, salary, department_id
from employees a, max_salary b
where a.department_id = b.department_id
and a.salary = b.salary