0

Question Description : The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

| Id | Name | Salary | DepartmentId |
| 1  | Joe    | 70000  |      1         |
| 2  | Henry | 80000  |      2         |
| 3  | Sam   | 60000  |      2         |
| 4  | Max   | 90000  |      1         |

The Department table holds all departments of the company.

| Id | Name     |
| 1  | IT       |
| 2  | Sales    |

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

| Department | Employee | Salary |
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |

My Question : My solution is the following, but it can only return max's salary not Henry's ; however if I remove "leetdepartment D"in the subquery, I can get 2 records (correct answer). Can anyone help me understand why I should remove "leetdepartment D" in the subquery ? 

SELECT D.name, E.name, E.salary 
from leetdepartment D, leetemployee E
where E.deparmentid = D.id
and   salary = (Select MAX(salary) from 
                leetemployee E, leetdepartment D
                where E.departmentid = D.id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
xiongbenxiong
  • 35
  • 1
  • 2
  • 6
  • (1) I removed the incompatible database tag. Only tag with the database you are really using. (2) *Never* use commas in the `FROM` clause. *Always* use proper, explicit `JOIN` syntax. – Gordon Linoff May 31 '17 at 23:35

2 Answers2

0
SELECT D.Name, E.Name, E.Salary 
FROM leetdepartment D
JOIN leetemployee E ON E.DepartmentId = D.Id
JOIN (SELECT MAX(Salary) AS MaxSalary, DepartmentId 
      FROM leetemployee GROUP BY DepartmentId) M
      ON M.DepartmentId = D.Id
WHERE M.MaxSalary = E.Salary
ORDER BY D.Id;
z m
  • 1,493
  • 2
  • 19
  • 21
0

you can do top 1 with ties as below

SELECT top (1) with ties D.name as [Department], E.name as [Employee], E.Salary 
    FROM leetemployee E JOIN leetdepartment D 
on E.deparmentid = D.id
    order by row_number() over (partition by D.name order by E.Salary DESC)
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38