Highest Salary in Each Department
Consider two tables:
Employees table has the columns:
salary (int), and
department_id (int). The
Departments table has the columns:
id (int) and
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
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.