SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get higher salary count in every department
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/05/2012 :  05:16:54  Show Profile  Reply with Quote
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
2210 Posts

Posted - 11/05/2012 :  05:23:31  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 11/05/2012 :  05:51:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/05/2012 :  05:55:47  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/05/2012 :  06:30:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/05/2012 :  06:44:04  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/05/2012 :  07:12:16  Show Profile  Reply with Quote
yeah man right thanks for this query it worked

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

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/05/2012 :  07:13:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/05/2012 :  07:21:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000