16,003,277 members
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
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

## Solution 1

```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```

## Solution 2

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```