Friday, 29 May 2015

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,
TAMATAM

1 comment:

  1. I blog quite often and I truly thank you for your content.
    Your article has really peaked my interest. I will book mark your
    website and keep checking for new information about
    once a week. I subscribed to your RSS feed too.

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts