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 SALARY1 A 10 102 b 20 203 c 30 304 d 10 1005 e 20 2006 f 30 3007 g 10 2008 h 20 2019 i 30 200 and dept with follwoig recordsDEPTNO102030and 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 empwhere EmpNo in ( SELECT EmpNo FROM emp WHERE Salary in ( SELECT Max(Salary) FROM emp GROUP BY DeptNo ) ) |
|
|
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,300Thus, 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 MaxSalaryfrom Empgroup 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) MaxSalariesINNER JOIN EmpON Emp.DeptNo = MaxSalaries.DeptNo AND Emp.Salary = MaxSalaries.MaxSalary - Jeff |
|
|
ashishnaik1
Starting Member
5 Posts |
Posted - 2003-09-11 : 08:49:08
|
Thanks jsmith8858 for reply. Great solution |
|
|
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. |
|
|
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 iwhere e.dept_id=i.dept_id group by dept_id) |
|
|
Howard43Willard
Starting Member
8 Posts |
Posted - 2012-12-02 : 20:20:55
|
work it out on paper |
|
|
rajuss
Starting Member
1 Post |
Posted - 2013-02-13 : 06:21:13
|
The below query gives the expected resultSELECT * FROM EMPWHERE (DEPT,SAL)IN (SEL DEPT,MAX(SAL) FROM EMP GROUP BY DEPT) |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-02-14 : 01:56:09
|
quote: Originally posted by rajuss The below query gives the expected resultSELECT * FROM EMPWHERE (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 ORACLEMadhivananFailing to plan is Planning to fail |
|
|
|