Comments by HarishRao4

HarishRao4 24-Nov-17 4:37am View

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

HarishRao4 24-Nov-17 4:35am View

I am able to achieve it with the below 2 queries, but what I need is a single query:

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

HarishRao4 24-Nov-17 2:22am View

Thanks so much, but I was looking for Department and Incentive Amount

HarishRao4 24-Nov-17 2:04am View

tbl_Employee
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

tbl_Incentives
Employee_ID Incentive
2 5000
3 1500
4 7000
5 1100

HarishRao4 24-Nov-17 2:02am View

One is an Employee and the other is an Incentives table

HarishRao4 24-Nov-17 2:02am View

Employee_ID Incentive
2 5000
3 1500
4 7000
5 1100

HarishRao4 24-Nov-17 2:01am View

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 1:54am View

SELECT TOP (1)
[Department]
,MAX(Salary) AS [Max_Salary]
FROM [tbl_Employee]
GROUP BY [Department]
ORDER BY MAX([Salary]) DESC

HarishRao4 22-Sep-17 8:54am View

Thanks Mehdi, most of the tables have the default index, and a few don't as allowing duplicate entries are a mandate

Comments by HarishRao4 (Top 9 by date)