Write a query to find the highest salary in each department?
Interview preparation resource from Gate Smashers.
Group rows by department and calculate MAX(salary) for each group. SQL: SELECT department, MAX(salary) AS highest_salary FROM Employees GROUP BY department;
| employee_id | employee_name | department | salary | manager_id |
|---|---|---|---|---|
| 101 | Aarav | Engineering | 95000 | 105 |
| 102 | Meera | Engineering | 72000 | 105 |
| 103 | Kabir | Sales | 65000 | 106 |
| 104 | Isha | Sales | 92000 | 106 |
| 105 | Rohan | Engineering | 90000 | NULL |
| 106 | Neha | Sales | 88000 | NULL |
SELECT department, MAX(salary) AS highest_salary
FROM Employees
GROUP BY department;Direct answer
Group rows by department and compute the maximum salary for each group. The query returns one row per department with that department's highest salary.
How it works
The query aggregates rows by the department column and applies the MAX() aggregate function to the salary column to produce the highest salary per department.
- Result: Returns the highest salary value per department (one row per department).
- Limitations: Does not return which employee(s) earn that salary — only the salary value.
Returning employee details (preserve ties)
If you need the employee(s) who earn the highest salary in each department (including ties), use a window function such as DENSE_RANK() partitioned by department, or compare each employee's salary to the department maximum via a join or subquery.
- Option 1: DENSE_RANK() over (partition by department order by salary DESC) to pick top-ranked employees and preserve ties.
- Option 2: Join Employees to an aggregated subquery that selects department and MAX(salary) to filter employees whose salary equals the department maximum.
Sample data
The following Employees table is the dataset referenced by the query. Use it to validate results or to construct queries that return employee details along with the department maximums.