Friday, May 29, 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--------------------------------------------------------------#

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog