Oracle rewrite query using rank or dense

I have the following query and sample data, which is working fine. It finds the max salary for employee(s) for each department.

Although it works, I like to change it to use rank or dense_rank (unsure how to do this) to achieve the output I GENERATED below but

Note department_id=1 has 2 different employees making the exact salary and I need to keep the result like that. In addition, is there a way I can only compare e.department_id = d.department_id once?

Any help would be greatly appreciated. Below is my test CASE and sample data.


CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'SALES'  FROM DUAL;

CREATE TABLE employees (employee_id, manager_id, first_name, last_name, department_id, sal,
serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 1, 100000, 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron',1, 50000,'D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chang',1, 100000, 'A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Dunbar',1, 75000, 'A1425' FROM DUAL UNION ALL
SELECT 5, NULL, 'Emily', 'Eden',2, 90000, 'C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Finn',1, 88500,'C1726' FROM DUAL UNION ALL
SELECT 7,5, 'Grace', 'Gelfenbein',2, 55000, 'C1727' FROM DUAL;
 
  select 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name,
    e.sal
  from employees e join dept d on e.department_id = d.department_id
    where not exists
      ( select null
        from   employees 
        where  department_id = e.department_id
        and    sal > e.sal );

EMPLOYEE_ID    FIRST_NAME    LAST_NAME    DEPARTMENT_ID    DEPARTMENT_NAME    SAL
1    Alice    Abbot    1    IT    100000
3    Carol    Chang    1    IT    100000
5    Emily    Eden    2    SALES    90000

CodePudding user response：

You may use RANK as follows:

WITH cte AS (
    SELECT e.employee_id, e.first_name, e.last_name, e.department_id,
           d.department_name, e.sal,
           RANK() OVER (PARTITION BY e.department_id ORDER BY e.sal DESC) rnk
    FROM employees e
    INNER JOIN dept d ON e.department_id = d.department_id
)

SELECT employee_id, first_name, last_name, department_id, department_name, sal
FROM cte
WHERE rnk = 1;