Employees with largest salary in department

Question

I found a couple of SQL tasks on Hacker News today, however I am stuck on solving the second task in Postgres, which I'll describe here:

You have the following, simple table structure:

List the employees who have the biggest salary in their respective departments.

I set up an SQL Fiddle here for you to play with. It should return Terry Robinson, Laura White. Along with their names it should have their salary and department name.

Furthermore, I'd be curious to know of a query which would return Terry Robinsons (maximum salary from the Sales department) and Laura White (maximum salary in the Marketing department) and an empty row for the IT department, with null as the employee; explicitly stating that there are no employees (thus nobody with the highest salary) in that department.

Answer 1

SQL query:

select d.name,e.name,e.salary
from employees e, depts d
where e.dept_id = d.id
and (d.id,e.salary) in
(select dept_id,max(salary) from employees group by dept_id);

Answer 2

Returns one or more people for each department with the highest salary:

SELECT result.Name Department, Employee2.Name Employee, result.salary Salary 
FROM ( SELECT dept.name, dept.department_id, max(Employee1.salary) salary 
       FROM Departments dept 
       JOIN Employees Employee1 ON Employee1.department_id = dept.department_id 
       GROUP BY dept.name, dept.department_id ) result 
JOIN Employees Employee2 ON Employee2.department_id = result.department_id 
WHERE Employee2.salary = result.salary

Answer 3

Assuming Postgres

Return highest salary with employee details, assuming table name emp having employees department with dept_id

select e1.* from emp e1  inner join (select max(sal) avg_sal,dept_id from emp group by dept_id) as e2 on e1.dept_id=e2.dept_id and e1.sal=e2.avg_sal

Answer 4

`select d.Name, e.Name, e.Salary from Employees e, Departments d,
(select DepartmentId as DeptId, max(Salary) as Salary
from Employees e
group by DepartmentId) m
where m.Salary = e.Salary
and m.DeptId = e.DepartmentId
and e.DepartmentId = d.DepartmentId`

The max salary of each department is computed in inner query using GROUP BY. And then select employees who satisfy those constraints.

Answer 5

SELECT 
    e.first_name, d.department_name, e.salary 
FROM 
    employees e 
JOIN 
    departments d 
ON 
    (e.department_id = d.department_id) 
WHERE 
    e.first_name 
IN
    (SELECT TOP 2 
        first_name 
    FROM 
        employees
    WHERE 
        department_id = d.department_id);

Answer 6

Table1 is emp - empno, ename, sal, deptno

Table2 is dept - deptno, dname.

Query could be (includes ties & runs on 11.2g):

select e1.empno, e1.ename, e1.sal, e1.deptno as department

from emp e1

where e1.sal in 

(SELECT  max(sal) from emp e, dept d where e.deptno = d.deptno group by d.dname)

 order by e1.deptno asc; 

Answer 7

Good old classic sql:

select e1.name, e1.salary, e1.department_id
from employees e1
where e1.salary=
  (select maxsalary=max(e.salary)  --, e. department_id
     from employees e 
     where e.department_id = e1.department_id 
   group by e.department_id
   )

Answer 8

Return one employee with the highest salary per dept.

Use DISTINCT ON for a much simpler and faster query that does all you are asking for:

SELECT DISTINCT ON (d.id)
       d.id AS department_id, d.name AS department
      ,e.id AS employee_id, e.name AS employee, e.salary
FROM   departments d
LEFT   JOIN employees e ON e.department_id = d.id
ORDER  BY d.id, e.salary DESC;

->SQLfiddle (for Postgres).

Also note the LEFT [OUTER] JOIN that keeps departments with no employees in the result.

This picks only one employee per department. If there are multiple sharing the highest salary, you can add more ORDER BY items to pick one in particular. Else, an arbitrary one is picked from peers.
If there are no employees, the department is still listed, with NULL values for employee columns.

You can simply add any columns you need in the SELECT list.

Find a detailed explanation, links and a benchmark for the technique in this related answer:
Select first row in each GROUP BY group?

Aside: It is an anti-pattern to use non-descriptive column names like name or id. Should be employee_id, employee etc.

Return all employees with the highest salary per dept.

Use the window function rank() (like @Scotch already posted, just simpler and faster):

SELECT d.name AS department, e.employee, e.salary
FROM   departments d
LEFT   JOIN (
   SELECT name AS employee, salary, department_id 
         ,rank() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
   FROM   employees e
   ) e ON e.department_id = d.department_id AND e.rnk = 1;

Same result as with the above query with your example (which has no ties), just a bit slower.

Answer 9

This is basically what you want. Rank() Over

SELECT ename ,
       departments.name
FROM ( SELECT ename ,
              dname
       FROM ( SELECT employees.name as ename ,
                     departments.name as dname , 
                     rank() over (
                       PARTITION BY employees.department_id 
                       ORDER BY employees.salary DESC
                       )
              FROM Employees
              JOIN Departments on employees.department_id = departments.id
            ) t
       WHERE rank = 1
     ) s
RIGHT JOIN departments on s.dname = departments.name

Answer 10

Take look at this solution SELECT MAX(E.SALARY), E.NAME, D.NAME as Department FROM employees E INNER JOIN DEPARTMENTS D ON D.ID = E.DEPARTMENT_ID GROUP BY D.NAME

Answer 11

This is with reference to your fiddle:

SELECT * -- or whatever is your columns list.
  FROM employees e JOIN departments d ON e.Department_ID = d.id
 WHERE (e.Department_ID, e.Salary) IN (SELECT Department_ID, MAX(Salary)
                                         FROM employees
                                     GROUP BY Department_ID)

EDIT :

As mentioned in a comment below, if you want to see the IT department also, with all NULL for the employee records, you can use the RIGHT JOIN and put the filter condition in the joining clause itself as follows:

SELECT e.name, e.salary, d.name -- or whatever is your columns list.
  FROM employees e RIGHT JOIN departments d ON e.Department_ID = d.id
   AND (e.Department_ID, e.Salary) IN (SELECT Department_ID, MAX(Salary)
                                         FROM employees
                                     GROUP BY Department_ID)