Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get higher salary count in every department

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-05 : 05:16:54
Hello all,

I had 2 tables like Department and employee

dept employee
1 1,2,3,4,5,6

2 1,2,3,4,5,6
3 1,2,3,4,5,6


for every department they have 10 employees i need to show highest salary of a employee of every department in query i am not getting proper logic for this help me out

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 05:23:31
If you are having both department_id, salary columns in EMPLOYEE table, then

SELECT departmnet_id, max(salary) 'MaxSalary'
FROM employee
GROUP BY department_id


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-05 : 05:51:21
no it is not working i have dept id in employee table and i don't want to show highest salary for all employess in which department who is getting high sal

P.V.P.MOhan
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 05:55:47
Am not getting your point. Post sample input and expected resultset

Oh you need to show employee_id right??
--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-05 : 06:30:34
basing on the both tables we need to get result

1 dept having 10 employess
2 dept have 10 employess

one to many relation

P.V.P.MOhan
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 06:44:04
one dept having more than one employee. As per my understanding you want to show the department and the employees who are getting highest salary in that corresponding department... Right??

;with cte
AS (SELECT department_id, employee_id, salary
FROM employees e1
WHERE salary = (SELECT MAX(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
)
)
SELECT distinct department_id,
STUFF((SELECT ',' + cast(s.employee_id as varchar(10)) FROM cte s WHERE s.department_ID = t.department_ID FOR XML PATH('')),1,1,'') AS empid
FROM cte t


If this is not your requirement explore the scenario

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-05 : 07:12:16
yeah man right thanks for this query it worked

P.V.P.MOhan
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 07:13:19
quote:
Originally posted by mohan123

yeah man right thanks for this query it worked

P.V.P.MOhan


You are Welcome

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-05 : 07:21:46
Alternate solution is as follows:

;with cte
AS (SELECT department_id, employee_id,
RANK() over(PARTITION by department_id order by salary desc) rn
FROM employees
)
SELECT distinct department_id,
STUFF((SELECT ',' + cast(s.employee_id as varchar(10)) FROM cte s WHERE s.department_ID = t.department_ID AND s.rn = 1 FOR XML PATH('')),1,1,'') AS empid
FROM cte t
WHERE t.rn = 1



--
Chandu
Go to Top of Page
   

- Advertisement -