Find top salary per department - is there a more efficient query?

Question

I have a query that works but I suspect I'm doing this inefficiently. Is there a more elegant approach to find the top salary in each department and the employee that earns it?

I'm doing a cte to find the max salary per dept id and then join that up with the employee data by matching salary and dept id. I have code below to build/populate the tables and the query at the end.

CREATE TABLE employee (
emplid SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
salary FLOAT NOT NULL,
depid INTEGER
);

INSERT INTO employee (name, salary, depid)
VALUES
('Chris',23456.99,1),
('Bob',98756.34,1),
('Malin',34567.22,2),
('Lisa',34967.73,2),
('Deepak',88582.22,3),
('Chester',99487.41,3);

CREATE TABLE department (
depid SERIAL PRIMARY KEY,
deptname VARCHAR NOT NULL
);

INSERT INTO department (deptname)
VALUES
('Engineering'),
('Sales'),
('Marketing');

--top salary by department
WITH cte AS (
SELECT d.depid, deptname, MAX(salary) AS maxsal
FROM employee e
JOIN department d ON d.depid = e.depid
GROUP BY d.depid, deptname
)
SELECT cte.deptname, e.name, cte.maxsal
FROM cte
JOIN employee e ON cte.depid = e.depid
AND e.salary = cte.maxsal
ORDER BY maxsal DESC;

Here is the target result:

"Marketing" "Chester" "99487.41" "Engineering" "Bob" "98756.34" "Sales" "Lisa" "34967.73"

Related: [Why not use Double or Float to represent currency?](https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency) — Tometzky, May 16 '19 at 07:33

score 1 · Accepted Answer · answered May 16 '19 at 06:08

In Postgres this can solved using the distinct on () operator:

SELECT distinct on (d.depid) d.depid, deptname, e.name, e.salary AS maxsal
FROM employee e
  JOIN department d ON d.depid = e.depid
order by d.depid, e.salary desc;

Or you can use a window function:

select depid, deptname, emp_name, salary
from (
  SELECT d.depid, 
         deptname, 
         e.name as emp_name, 
         e.salary, 
         max(e.salary) over (partition by d.depid) AS maxsal
  FROM employee e
    JOIN department d ON d.depid = e.depid
) t
where salary = maxsal;

Online example: https://rextester.com/MBAF73582

score 0 · Answer 2 · answered May 16 '19 at 08:03

0

You should have an index:

create index employee_depid_salary_desc_idx on employee(depid, salary desc);

And then use the following query that can use the index:

select
  depid,
  deptname,
  (
    select
      emplid
    from employees
    where depid=department.depid
    order by salary desc
    limit 1
  ) as max_salaried_emplid
from department;

(A join for retrieving data from the emplid left as an exercise for the reader).

answered May 16 '19 at 08:03

Tometzky

22,573
5
59
73

A warning: if there are 2 or more employees with max salary within a department the query above will retrieve one of them and it's not defined which one. – Tometzky May 16 '19 at 08:06

Find top salary per department - is there a more efficient query?

2 Answers2