To calculate the highest salary in each department, there are several ways you can approach this problem in SQL. Below are all possible solutions, ranging from basic queries using aggregation to more advanced techniques involving window functions and subqueries. The examples assume a table structure with columns such as EmployeeId, DepartmentId, Name, and Salary.
Sample Table
Assume the following Employee table for all examples:
- Using GROUP BY and MAX()
The most common and straightforward approach is to use the MAX() function in conjunction with GROUP BY to find the highest salary for each department.
Query:
SELECT DepartmentId, MAX(Salary) AS HighestSalary
FROM Employee
GROUP BY DepartmentId;
Explanation:
GROUP BY DepartmentId groups the results by department.
MAX(Salary) finds the maximum salary for each department.
- Using a Correlated Subquery
You can also use a correlated subquery to find the highest salary for each department.
Query:
SELECT EmployeeId, DepartmentId, Salary
FROM Employee e
WHERE Salary = (SELECT MAX(Salary) FROM Employee WHERE DepartmentId = e.DepartmentId);
Explanation:
The subquery SELECT MAX(Salary) finds the maximum salary for the department corresponding to each row of the outer query.
The outer query returns the employees whose salaries match the maximum salary in their department.
- Using ROW_NUMBER() Window Function
If you need not only the maximum salary but also the corresponding employee details, you can use the ROW_NUMBER() function to rank employees within each department based on their salary.
Query:
WITH RankedSalaries AS (
SELECT EmployeeId, DepartmentId, Salary,
ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank
FROM Employee
)
SELECT EmployeeId, DepartmentId, Salary
FROM RankedSalaries
WHERE Rank = 1;
Explanation:
ROW_NUMBER() ranks employees in each department based on their salary.
The PARTITION BY DepartmentId ensures that the ranking is done within each department.
The outer query filters the rows where the rank is 1, which corresponds to the highest salary.
- Using DENSE_RANK() Window Function
Unlike ROW_NUMBER(), DENSE_RANK() can handle ties, ensuring that multiple employees with the highest salary are ranked equally.
Query:
WITH RankedSalaries AS (
SELECT EmployeeId, DepartmentId, Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank
FROM Employee
)
SELECT EmployeeId, DepartmentId, Salary
FROM RankedSalaries
WHERE Rank = 1;
Explanation:
DENSE_RANK() ensures that employees with equal salaries receive the same rank.
The outer query filters out rows where Rank = 1, which represents the highest salary in each department.
- Using GROUP BY with JOIN
You can first calculate the maximum salary for each department using GROUP BY, and then join this result with the original table to retrieve employee details.
Query:
WITH MaxSalaries AS (
SELECT DepartmentId, MAX(Salary) AS HighestSalary
FROM Employee
GROUP BY DepartmentId
)
SELECT e.EmployeeId, e.DepartmentId, e.Salary
FROM Employee e
JOIN MaxSalaries ms
ON e.DepartmentId = ms.DepartmentId AND e.Salary = ms.HighestSalary;
Explanation:
The MaxSalaries CTE calculates the maximum salary for each department.
The main query joins the Employee table with the MaxSalaries CTE to fetch employee details where their salary matches the highest salary for their department.
- Using DISTINCT with ORDER BY and LIMIT (or TOP)
For databases that support LIMIT (or TOP in SQL Server), you can use this to select the highest salary after ordering.
Query (MySQL/PostgreSQL):
SELECT DISTINCT DepartmentId, Salary
FROM Employee
WHERE DepartmentId = 'IT'
ORDER BY Salary DESC
LIMIT 1;
Query (SQL Server):
SELECT TOP 1 DepartmentId, Salary
FROM Employee
WHERE DepartmentId = 'IT'
ORDER BY Salary DESC;
Explanation:
The query orders employees by salary in descending order and selects the top salary for each department.
- Using GROUP BY with HAVING
If you want to include filtering logic for specific departments, you can use HAVING with GROUP BY to filter the departments after aggregation.
Query:
SELECT DepartmentId, MAX(Salary) AS HighestSalary
FROM Employee
GROUP BY DepartmentId
HAVING DepartmentId IN ('HR', 'IT');
Explanation:
HAVING DepartmentId IN ('HR', 'IT') ensures that only departments HR and IT are included in the result.
The query calculates the highest salary for the filtered departments.
- Using RANK() Window Function
Another option to handle ties in salaries is the RANK() function, which provides a rank but skips over ranks in the event of ties.
Query:
WITH RankedSalaries AS (
SELECT EmployeeId, DepartmentId, Salary,
RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank
FROM Employee
)
SELECT EmployeeId, DepartmentId, Salary
FROM RankedSalaries
WHERE Rank = 1;
Explanation:
RANK() assigns the same rank to employees with equal salaries, but the next rank will be skipped.
The outer query selects only the employees with the highest salary in each department.
- Using TOP with PARTITION BY (SQL Server)
SQL Server allows a TOP query with PARTITION BY to achieve the same result without needing a subquery.
Query:
SELECT TOP 1 WITH TIES EmployeeId, DepartmentId, Salary
FROM Employee
ORDER BY ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC);
Explanation:
TOP 1 WITH TIES ensures that all employees with the highest salary in each department are selected.
PARTITION BY DepartmentId ensures that the ranking is done per department.
- Using UNION for Multiple Departments (Manual Approach)
If you are only interested in a few specific departments, you can manually calculate the highest salary for each department and combine the results using UNION.
Query:
SELECT MAX(Salary) AS HighestSalary, 'HR' AS DepartmentId
FROM Employee
WHERE DepartmentId = 'HR'
UNION
SELECT MAX(Salary) AS HighestSalary, 'IT' AS DepartmentId
FROM Employee
WHERE DepartmentId = 'IT';
Explanation:
This is a manual approach where you calculate the highest salary for each department individually and combine the results using UNION.
Conclusion
These are all the possible solutions for calculating the highest salary in each department using SQL. The best approach depends on your specific use case:
For simplicity: Use GROUP BY with MAX().
For handling ties: Use DENSE_RANK() or RANK().
For retrieving employee details: Use a correlated subquery or window functions like ROW_NUMBER().
Top comments (0)