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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Finding Department wise Max. Salary

Author  Topic 

ashishnaik1
Starting Member

5 Posts

Posted - 2003-09-10 : 15:39:04
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

552 Posts

Posted - 2003-09-10 : 15:53:16
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

7423 Posts

Posted - 2003-09-10 : 16:05:42
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
Go to Top of Page

ashishnaik1
Starting Member

5 Posts

Posted - 2003-09-11 : 08:49:08
Thanks jsmith8858 for reply. Great solution
Go to Top of Page

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-11 : 09:02:09
I usually use the join solution as it is often the most efficient and correct solution.

Go to Top of Page

vinaykvasoya
Starting Member

1 Post

Posted - 2012-11-23 : 00:24:40
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

8 Posts

Posted - 2012-12-02 : 20:20:55
work it out on paper





Go to Top of Page

rajuss
Starting Member

1 Post

Posted - 2013-02-13 : 06:21:13
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

22864 Posts

Posted - 2013-02-14 : 01:56:09
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
   

- Advertisement -