Challenge
Consider two tables: Employees and Departments. The Employees table has the columns: id (int), name (varchar), salary (int), and department_id (int). The Departments table has the columns: id (int) and name (varchar).
Write a SQL query to find the employee with the highest salary in each department.
DB Fiddle Instructions
Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.
The subquery groups the data by department id and then for each department id, it finds the maximum salary. This will give us a list of maximum salaries for each department.
Then in the main query, we join the Employees and Departments tables on their matching department_id fields. We then only select rows where the department_id and salary combination match what we found in the subquery (i.e., the maximum salary in each department).
The result is the name of each department along with the name and salary of the employee in that department who has the highest salary.