184: Solution with step by step explanation
Intuition\n Describe your first thoughts on how to solve this problem. \n\n# Approach\nLet\'s go through the steps:\n\nJoin the Employee and Department tables o
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\n\n# Approach\nLet\'s go through the steps:\n\nJoin the Employee and Department tables on the departmentId column to get the name of the department for each employee.\nUse a subquery to get the maximum salary for each department. The subquery first groups the employees by departmentId, and then gets the maximum salary for each group using the MAX function.\nJoin the result of the subquery with the Employee table on the departmentId and salary columns to get the employees who have the maximum salary for their department.\nSelect the Department, Employee, and Salary columns from the result.\nThis query will return the department name, employee name, and their salary for each department where at least one employee has the highest salary. The result will be ordered by department name, but the order of the rows within each department is not specified.\n\n# Complexity\n- Time complexity:\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Space complexity:\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n\n# Code\nTo find the employees who have the highest salary in each of the departments, we can use a subquery to get the maximum salary for each department, and then join it with the employee table to get the employees who have the maximum salary for their department.\n\nHere\'s the SQL query:\n\n```\nSELECT d.name AS Department, e.name AS Employee, e.salary AS Salary\nFROM Employee e\nJOIN Department d ON e.departmentId = d.id\nWHERE (e.departmentId, e.salary) IN\n    (SELECT departmentId, MAX(salary)\n     FROM Employee\n     GROUP BY departmentId)\n\n```
['Database', 'MySQL']