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 employee1 1,2,3,4,5,62 1,2,3,4,5,63 1,2,3,4,5,6for 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 outP.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, thenSELECT departmnet_id, max(salary) 'MaxSalary'FROM employeeGROUP BY department_id --Chandu |
|
|
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 salP.V.P.MOhan |
|
|
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 resultsetOh you need to show employee_id right??--Chandu |
|
|
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 employessone to many relationP.V.P.MOhan |
|
|
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 empidFROM cte t If this is not your requirement explore the scenario--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-11-05 : 07:12:16
|
yeah man right thanks for this query it workedP.V.P.MOhan |
|
|
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 workedP.V.P.MOhan
You are Welcome--Chandu |
|
|
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 empidFROM cte t WHERE t.rn = 1 --Chandu |
|
|
|