We can get the department-wise highest salary by leveraging SQL functions like ROW_NUMBER() or using GROUP BY with a JOIN. These approaches help us identify the top earners in each department efficiently from the Employee table.
Employee Table:
CREATE TABLE dbo.Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100),
Gender CHAR(1),
Department VARCHAR(50),
Salary INT,
JoiningDate DATE,
ManagerID INT,
IsActive BIT
);
GO
INSERT INTO dbo.Employee VALUES
(101, 'Shaun Abbot', 'M', 'IT', 88000, '2020-03-15', NULL, 1),
(102, 'Stefee Taylor', 'F', 'HR', 88000, '2020-07-01', 105, 1),
(103, 'Mathew Bulmer', 'M', 'Finance', 75000, '2021-01-20', 104, 1),
(104, 'Jennat Warner', 'F', 'Finance', 98000, '2021-10-05', NULL, 1),
(105, 'Jonathan Perry', 'M', 'HR', 95000, '2019-12-12', NULL, 1),
(106, 'Lisa Devine', 'F', 'IT', 85000, '2021-06-11', 101, 1),
(107, 'Adam Trumen', 'M', 'IT', 50000, '2019-04-01', 101, 1),
(108, 'Sarah Mclean', 'F', 'Marketing', 95000, '2023-01-11', NULL, 1),
(109, 'Steve Maxwell', 'M', 'IT', 75000, '2025-12-01', 101, 0),
(110, 'Georgia Havells', 'F', 'Finance', 45000, '2025-09-01', 104, 1);
Using Group By & JOIN:
SELECT
E.EmpID
,E.EmpName
,E.Department
,E.Salary
FROM
(
SELECT [Department]
,MAX([Salary]) AS [Salary]
FROM [dbo].[Employee]
GROUP BY [Department]
) D
INNER JOIN [dbo].[Employee] E
ON D.Department = E.Department
AND D.Salary = E.Salary
ORDER BY E.Department
- First, we create a derived table (D) that retrieves the highest salary for each department using GROUP BY and MAX(Salary).
- Next, we join the derived table (D) with the Employee table (E) on both Department and Salary.
- Since the derived table holds only the maximum salary per department, the result of the join will return only those employee(s) who earn the highest salary in their respective departments.
Using ROW_NUMBER()
WITH Department_wise_Ranking_Salary AS (
SELECT
EmpID,
EmpName,
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rnk
FROM dbo.Employee
)
SELECT EmpID, EmpName, Department, Salary
FROM Department_wise_Ranking_Salary
WHERE Rnk = 1;
- A Common Table Expression (CTE) named Department_wise_Ranking_Salary is created to assign a ranking to employees within each department based on Salary in descending order.
- The ROW_NUMBER() function is used with PARTITION BY Department to restart the ranking for every department. The employee with the highest salary gets Rnk = 1.
- The final SELECT query filters the result to return only those employees whose Rnk = 1, i.e., the top earners in each department.
