| Author |
 Topic  |
|
|
ashishnaik1
Starting Member
5 Posts |
 Posted - 09/10/2003 : 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
USA
552 Posts |
Posted - 09/10/2003 : 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
)
)
|
 |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/10/2003 : 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 |
Edited by - jsmith8858 on 09/10/2003 16:06:47 |
|
|
|
ashishnaik1
Starting Member
5 Posts |
Posted - 09/11/2003 : 08:49:08
|
| Thanks jsmith8858 for reply. Great solution |
|
|
|
drymchaser
Aged Yak Warrior
USA
552 Posts |
Posted - 09/11/2003 : 09:02:09
|
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 |
|
|
|
vinaykvasoya
Starting Member
India
1 Posts |
Posted - 11/23/2012 : 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) |
|
|
|
Howard43Willard
Starting Member
USA
8 Posts |
Posted - 12/02/2012 : 20:20:55
|
work it out on paper
 |
Edited by - Howard43Willard on 12/02/2012 20:25:56 |
|
|
|
rajuss
Starting Member
India
1 Posts |
Posted - 02/13/2013 : 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) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 02/14/2013 : 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 |
|
|
| |
Topic |
|
Finding Department wise Max. Salary