SQL Server Query to find Highest or Maximum Salary in each Department

SQL Query to Retrieve Highest or Maximum Salary drawing Employee Details from each Department

Example :
Suppose we have a Employee Table as follows:

EMP Table :

EMP_ID	F_Name	L_Name	Dept_ID	Cur_Address	Perm_Address	Salary
1001	Ravi	Kumar	1	Hyderabad	Hyderabad	20000
1002	David	Smith	2	Bangalore	Pune	35000
1003	Victory	Venkatesh	1	Hyderabad	Hyderabad	50000
1004	Tamatam	Reddy	3	Bangalore	Hyderabad	25000
1005	William	Smith	2	Hyderabad	Bangalore	40000
1006	King	Fisher	6	Bangalore	Bangalore	30000

Now if we want to Retrieve Highest or Maximum Salary drawing Employee Details of each Department , from the above table , we can use the following SQL Sub Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name,Dept_Id,Salary From EMP Where Salary IN (SELECT MAX(Salary)AS Max_Sal From EMP GROUP BY Dept_Id)

Output :

E_Id	E_Name	Dept_Id	Salary
1003	Victory_Venkatesh	1	50000
1004	Tamatam_Reddy	3	25000
1005	William_Smith	2	40000
1006	King_Fisher	6	30000

#--------------------------------------------------------------Thanks--------------------------------------------------------------#