Write an SQL query that finds the employee(s) who earn the maximum salary in each department.

Question

Assisting AI · Accepted Answer

A straightforward approach is to use a correlated subquery that compares each employee’s salary to the maximum salary in their department:

```sql
SELECT e.emp_name,
       e.department,
       e.salary
FROM   Employees e
WHERE  e.salary = (
        SELECT MAX(salary)
        FROM   Employees
        WHERE  department = e.department
       );
```

Alternatively, you can use a window function:

```sql
SELECT emp_name,
       department,
       salary
FROM   (
        SELECT emp_name,
               department,
               salary,
               MAX(salary) OVER (PARTITION BY department) AS max_sal
        FROM   Employees
       ) AS t
WHERE  salary = max_sal;
```

Both queries run in O(n) time with a single scan of the table, making them efficient for large datasets.

Write an SQL query that finds the employee(s) who earn the maximum salary in each department.

💡 Model Answer

🎤 Get questions like this answered in real-time