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 :
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.