Easy to understand Step-by-step Explanation 😄 (Beats 80%)
Intuition\n Describe your first thoughts on how to solve this problem. \n\nYou can solve this problem by using a combination of GROUP BY and MAX(salary). The tr
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\n\nYou can solve this problem by using a combination of `GROUP BY` and `MAX(salary)`. The tricky part is that more than one employee may have the maximum department salary. This means that we have to split the search process into several steps.\n\n1. Find the maximum salary for each department.\n2. Find the employees whose salary and department match the results from step 1.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n\nThis is your starting point. You can see that the highest salary for the IT department is 90000 and that Max and Jim both have this salary.\n\n```\nEmployee table:\n+----+-------+--------+--------------+\n| id | name  | salary | departmentId |\n+----+-------+--------+--------------+\n| 1  | Joe   | 70000  | 1            |\n| 2  | Jim   | 90000  | 1            | <-- highest in IT\n| 3  | Henry | 80000  | 2            | <-- highest in Sales\n| 4  | Sam   | 60000  | 2            |\n| 5  | Max   | 90000  | 1            | <-- highest in IT\n+----+-------+--------+--------------+\n\nDepartment table:\n+----+-------+\n| id | name  |\n+----+-------+\n| 1  | IT    |\n| 2  | Sales |\n+----+-------+\n```\n\n### Step 1 - Find the maximum salary for each department\n\nYou can use `GROUP BY` in combination with `MAX(salary)` to find the highest salary per department.\n\n```\n| departmentId | maxSalary |\n| ------------ | --------- |\n| 1            | 90000     |\n| 2            | 80000     |\n```\n\n### Step 2 - Find the employees whose salary and department match the results from step 1\n\nJoin the Employee and Department tables with the MaxSalary table on the shared departmentId column, then filter out all rows where the employee\'s salary does not equal the maximum salary value. This is similar to asking for a list of employees who have the maximum salary for their department.\n\n(Rename the columns to get the correct answer.)\n\n```\n| departmentId | maxSalary | Department.name | Employee.name | Employee.salary |\n| ------------ | --------- | --------------- | ------------- | --------------- |\n| 1            | 90000     | IT              | Jim           | 90000           |\n| 2            | 80000     | Sales           | Henry         | 80000           |\n| 1            | 90000     | IT              | Max           | 90000           |\n```\n\n# Code\n```\n--\n-- Step 1 - Find the maximum salary for each department\n--\nWITH\n  MaxSalary AS (\n  SELECT\n    Department.id departmentId,\n    MAX(salary) maxSalary\n  FROM\n    Employee\n  INNER JOIN\n    Department\n  ON\n    Employee.departmentId = Department.id\n  GROUP BY\n    Department.id\n)\n--\n-- Step 2 - Find the employees whose salary and department match the results from step 1\n--\nSELECT\n  Department.name Department,\n  Employee.name Employee,\n  Employee.salary Salary\nFROM\n  Employee\nINNER JOIN\n  MaxSalary\nON\n  Employee.departmentId = MaxSalary.departmentId\nINNER JOIN\n  Department\nON\n  Employee.departmentId = Department.id\nWHERE\n  Employee.salary = maxSalary\n\n```
['MySQL']