Find Highest Salary Employees by Department (Subquery & Join) in MS SQL Server
Intuition\nTo find the employee with the highest salary in each department using T-SQL, you need to:\n\n- Determine the maximum salary for each department.\n- F
# Intuition\nTo find the employee with the highest salary in each department using T-SQL, you need to:\n\n- Determine the maximum salary for each department.\n- Filter employees to include only those with the maximum salary within their respective departments.\n- Join this filtered data with the department names to get the desired result.\n\n# Approach\n1. `Subquery for Maximum Salary`: Create a subquery that groups the employees by departmentId and computes the maximum salary (MaxSalary) for each department.\n\n2. `Join Results`: Perform a join operation between the Employee table and the Department table, and then join this with the results of the subquery. This will give you the employees who have the maximum salary in their respective departments.\n\n3. `Select Relevant Columns`: Choose the columns that are needed for the final output: department name, employee name, and salary.\n\n# Complexity\n- Time complexity: $$O(nlogn)$$                                 \n`Subquery`: The subquery involves a GROUP BY operation, which is typically $$O(nlogn)$$ due to sorting.\n`Joins`: The join operations are generally $$O(n)$$ for each join step, assuming efficient indexing.\n\n- Space complexity: $$O(n)$$ space is required for intermediate results and the final join operation. The size of the intermediate results and final result set grows linearly with the number of rows in the tables.\n\n# Code\n```\n/* Write your T-SQL query statement below */\nSELECT \n    d.name AS Department, \n    e.name AS Employee, \n    e.salary AS Salary\nFROM \n    Employee e\nJOIN \n    Department d \n    ON d.id = e.departmentId\nJOIN \n    (\n        SELECT \n            departmentId, \n            MAX(salary) AS MaxSalary\n        FROM \n            Employee\n        GROUP BY \n            departmentId\n    ) max_salaries\n    ON e.departmentId = max_salaries.departmentId \n    AND e.salary = max_salaries.MaxSalary;\n```
['Database', 'MS SQL Server']