Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want the department name and the highest incentive value, whereas the below query returns all the departments and all the incentives. Can someone help?

What I have tried:

select Department, (select ISNULL(max(INCENTIVE_AMOUNT),0)
from tbl_INCENTIVES) as max_incentive
from tbl_Employee
Posted
Updated 24-Nov-17 1:26am
Comments
itsmypassion 24-Nov-17 1:48am    
Can you please mention sample data
HarishRao4 24-Nov-17 2:01am    
Employee_ID Employee_Name Department Salary
1 Harish Support 15000
2 Sudhakar Support 25000
3 Lekhana SimDoc 14000
4 Kishor LMS 45000
5 Pranav SimDoc 13000
HarishRao4 24-Nov-17 2:02am    
Employee_ID Incentive
2 5000
3 1500
4 7000
5 1100
HarishRao4 24-Nov-17 2:02am    
One is an Employee and the other is an Incentives table
HarishRao4 24-Nov-17 4:37am    
I am able to achieve with the below 2 queries, but I am looking for a single query to do the work:

select department, max(incentive_amount) as incentive_amount into DeptIncent
from tbl_employee
inner join tbl_incentives on employee_id = employee_ref_id
Group by department


select top 1 department, incentive_amount from DeptIncent order by incentive_amount desc

select Top 1 department, max(incentive_amount) as incentive_amount  
from tbl_employee
inner join tbl_incentives on employee_id = employee_ref_id
Group by department
 
Share this answer
 
SQL
select TOP 1 department, max(incentive_amount) as incentive_amount 
from tbl_employee
inner join tbl_incentives on employee_id = employee_ref_id
Group by department
Order by  max(incentive_amount) DESC
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900