CREATE TABLE EmployeeTbl (SNo INT, EName VARCHAR(20), Dept VARCHAR(20), Sal INT)
INSERT INTO EmployeeTbl VALUES(1, 'AAA', 'EEE', 1000),
(2, 'BBB', 'EEE', 10900),
(3, 'CCC', 'ECE', 1000),
(4, 'DDD', 'ECE', 10800),
(5, 'AB', 'EEE', 10500),
(6, 'ADA', 'ECE', 11000),
(7, 'ABSA', 'CS', 1000)
SELECT * FROM EmployeeTbl
;WITH cteRowNum AS (
SELECT Dept, EName, Sal,
DENSE_RANK()
OVER(PARTITION BY Dept ORDER BY Sal DESC) AS RowNum
FROM EmployeeTbl
)
SELECT Ename, Dept, EName, Sal
FROM cteRowNum
WHERE RowNum <=1;
Source
data and Target data like below format
