| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
203 Posts |
 Posted - 11/05/2012 : 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
Flowing Fount of Yak Knowledge
India
1450 Posts |
Posted - 11/05/2012 : 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 |
 |
|
|
mohan123
Posting Yak Master
India
203 Posts |
Posted - 11/05/2012 : 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1450 Posts |
Posted - 11/05/2012 : 05:55:47
|
Am not getting your point. Post sample input and expected resultset
Oh you need to show employee_id right??
--
Chandu |
Edited by - bandi on 11/05/2012 06:01:11 |
|
|
|
mohan123
Posting Yak Master
India
203 Posts |
Posted - 11/05/2012 : 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1450 Posts |
Posted - 11/05/2012 : 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 |
Edited by - bandi on 11/05/2012 07:09:48 |
|
|
|
mohan123
Posting Yak Master
India
203 Posts |
Posted - 11/05/2012 : 07:12:16
|
yeah man right thanks for this query it worked
P.V.P.MOhan |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1450 Posts |
Posted - 11/05/2012 : 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1450 Posts |
Posted - 11/05/2012 : 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 |
Edited by - bandi on 11/05/2012 07:31:02 |
|
|
| |
Topic |
|
How to get higher salary count in every department
