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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Finding Department wise Max. Salary
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashishnaik1
Starting Member

5 Posts

Posted - 09/10/2003 :  15:39:04  Show Profile  Reply with Quote
Hi all,
i am having 2 tables emp and dept with following data
EMPNO ENAME DEPTNO SALARY
1 A 10 10
2 b 20 20
3 c 30 30
4 d 10 100
5 e 20 200
6 f 30 300
7 g 10 200
8 h 20 201
9 i 30 200

and dept with follwoig records
DEPTNO
10
20
30

and Dept no is Primary key in DEPT and Foreign key in EMP. I want to find employee detail who draws maximum salary in there department...
I write follwing query;
select * from emp where salary in(select max(salary) from emp group by deptno)
but i get 2 records of dept 20 and 2 for 30.
Can any one tell me why is happens and what is the correct query to get said result.....
Thanks is advance...

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 09/10/2003 :  15:53:16  Show Profile  Reply with Quote
There are other ways but I think this should work:
SELECT	*
FROM	emp
where	EmpNo in 
	(
	SELECT	EmpNo
	FROM	emp
	WHERE	Salary in
		(
		SELECT	Max(Salary)
		FROM	emp
		GROUP BY DeptNo
		)
	)
	
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/10/2003 :  16:05:42  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:

select * from emp where salary in(select max(salary) from emp group by deptno)



work it out on paper. what does the inner query return? look at the results and you'll see that it returns a list of max salaries:

200,201,300

Thus, your final query returns ANYONE with a salary of 200,201 or 300 -- regardless of what department they are in.

drymchaser -- yours does the same thing, but adds another layer making it even less efficient.

So what's a solution?

First, we need to find the Max(salary) of each department:


select 
  deptno, max(salary) as MaxSalary
from
  Emp
group by 
  DeptNo


That returns the department # and what the max salary of that department is. Now, we just join to our original table on DeptNo and MaxSalary and that gives us our result:


SELECT
  Emp.*
FROM 
  (Above SQL) MaxSalaries
INNER JOIN
  Emp
ON
  Emp.DeptNo = MaxSalaries.DeptNo AND
  Emp.Salary  = MaxSalaries.MaxSalary


- Jeff

Edited by - jsmith8858 on 09/10/2003 16:06:47
Go to Top of Page

ashishnaik1
Starting Member

5 Posts

Posted - 09/11/2003 :  08:49:08  Show Profile  Reply with Quote
Thanks jsmith8858 for reply. Great solution
Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 09/11/2003 :  09:02:09  Show Profile  Reply with Quote
I usually use the join solution as it is often the most efficient and correct solution.


Edited by - drymchaser on 09/11/2003 09:08:27
Go to Top of Page

vinaykvasoya
Starting Member

India
1 Posts

Posted - 11/23/2012 :  00:24:40  Show Profile  Reply with Quote
select dept_id,emp_name,salary from emp_info as e
where e.salary IN(select MAX(salary) from emp_info as i
where e.dept_id=i.dept_id group by dept_id)
Go to Top of Page

Howard43Willard
Starting Member

USA
8 Posts

Posted - 12/02/2012 :  20:20:55  Show Profile  Reply with Quote
work it out on paper






Edited by - Howard43Willard on 12/02/2012 20:25:56
Go to Top of Page

rajuss
Starting Member

India
1 Posts

Posted - 02/13/2013 :  06:21:13  Show Profile  Reply with Quote
The below query gives the expected result

SELECT * FROM EMP
WHERE (DEPT,SAL)
IN (SEL DEPT,MAX(SAL) FROM EMP GROUP BY DEPT)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 02/14/2013 :  01:56:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by rajuss

The below query gives the expected result

SELECT * FROM EMP
WHERE (DEPT,SAL)
IN (SEL DEPT,MAX(SAL) FROM EMP GROUP BY DEPT)


I doubt that this will work in SQL Server. Your code can run well in ORACLE

Madhivanan

Failing to plan is Planning to fail
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.05 seconds. Powered By: Snitz Forums 2000