Details of Employee who is having highest salary from each Department

Details of Employee who is having highest salary from each Department

CREATE TABLE Employee (EmpId int, Name Varchar(20), Department Varchar(20), Salary Int)

INSERT INTO Employee values (1,'Binod','Dot Net', 1000),

(2,'Anil','Dot Net', 2000),

(3,'Rohit','Dot Net', 1500),

(4,'Ankit','Design', 3000),

(5,'Mandeep','Design', 2000),

(6,'Bharat','Design', 3040),

(7,'Harinder','PHP', 4000),

(8,'Neha','PHP', 2000),

(9,'Airi','PHP', 4300),

(10,'Rajesh','PHP', 4300)

EmpId	Name	Department	Salary
1	Binod	Dot Net	1000
2	Anil	Dot Net	2000
3	Rohit	Dot Net	1500
4	Ankit	Design	3000
5	Mandeep	Design	2000
6	Bharat	Design	3040
7	Harinder	PHP	4000
8	Neha	PHP	2000
9	Airi	PHP	4300
10	Rajesh	PHP	4300

Expected Output :

Name	Department	Salary
Bharat	Design	3040
Anil	Dot Net	2000
Airi	PHP	4300
Rajesh	PHP	4300

Query:

WITH CTE

AS

(

SELECT Name, Department,Salary,DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum FROM Employee

)

SELECT Name, Department,Salary FROM CTE WHERE RowNum=1

NOTE: In above query we have used DENSE_RANK() function and we can't use ROW_NUMBER() function because it will not generate same row number for same salary from same department but DENSE_RANK() will generate same row number.