Can someone tell me what's wrong with this query

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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 23-Nov-17 18:49pm

HarishRao4

Updated 24-Nov-17 1:26am

Add a Solution

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

Paw Jershauge 24-Nov-17 1:51am

Do you want ONLY the department with the highest incentives ???

HarishRao4 24-Nov-17 1:54am

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

Paw Jershauge 24-Nov-17 2:16am

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

HarishRao4 24-Nov-17 2:22am

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

Paw Jershauge 24-Nov-17 2:26am

your welcome ;)
Use Row_number() if you want to make ranking column. so you could have lets say Top10 Departments with their Salaries

,ROW_NUMBER() OVER(ORDER BY MAX([salary]) DESC) AS [Max_Salary_Rank]

HarishRao4 24-Nov-17 4:35am

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:04am

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

2 solutions

Add a Solution

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

HarishRao4 · Answer 1 · 2017-11-23T22:48:00

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

Posted 23-Nov-17 22:48pm

HarishRao4

itsmypassion · Answer 2 · 2017-11-24T01:26:00

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

Posted 24-Nov-17 1:26am

itsmypassion

Can someone tell me what's wrong with this query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Can someone tell me what's wrong with this query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us