Subquery

[imfarhan]

Hi Alls
Just the basic questions, I'm trying to get the result from the EMP table.
I can get the max(sal) from each deptno, but I would like to see the max(sal) from which department.
<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>

Query21


</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>deptno</TH><TH borderColor=#000000 bgColor=#c0c0c0>MaxOfsal</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>10</TD><TD borderColor=#c0c0c0 align=right>2000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>20</TD><TD borderColor=#c0c0c0 align=right>4000</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>30</TD><TD borderColor=#c0c0c0 align=right>1000</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I have used the following query but not working

SELECT emp.deptno, max(emp.sal)
FROM emp
GROUP BY emp.deptno
having max(emp.sal) >=  (Select max(emp.sal) from emp group by deptno)

So th Result should be ... 20 , 4000

Thanks in advance!

Regards
Farhan

 

[CharlesChuckieCharles]

surely you only need
SELECT emp.deptno, max(emp.sal)
FROM emp
GROUP BY emp.deptno

to get the maximum salary by department Code

Your HAVING clause appears to be redundant?

maybe you should illustrate what you'd expect to see?

 

[imfarhan]

Thanks for reply,
In my SQL I'm getting all the department with their maximum salary.
I want to print only the maximum salary within those department Not other two departments.

so, within those department I need to whic department has maximum salary ... I you know what I mean ?

Many thanks
F