nagaraju_thumati

• Oct 23rd, 2006

 

ya u can use this quiery, i think no need to use dept table bcz already emp has that attribute select max(sal) from empwhere sal=any ( select max(sal) from emp group by deptno);

sarfaraz ahmad

• Oct 25th, 2006

 

select max(a.sal),b.deptname from emp a,dept b where a.deptno=b.deptno group by b.deptname

Srinivas

• Oct 26th, 2006

 

both answers r wrong, i want the greatest salaries group of sum(sal).

means if deptno 10 have sum(sal)=10000

                       20 have sum(sal)=15000

                       30 have sum(sal)=12000

i need answer is : 20   15000

beacause dept 20 have greatest sum.

Srinivas

Padmanabham

• Oct 30th, 2006

 

select deptno,max(sum(salary))

from EMP group by deptno;

BRAJESH KUMAR

• Nov 2nd, 2006

 

I TAKE THE EMP AND DEPT TABLE FROM SCOTT AND TIGER.

SELECT * FROM (SELECT SUM(SAL)MAXSAL,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO GROUP BY DNAME ORDER BY SUM(SAL)DESC )WHERE ROWNUM=1

tanveer

• Nov 2nd, 2006

 

select deptno,sum(sal)
from emp
group by deptno
having (deptno,sum(sal)=
                              select deptno
from emp
group by deptno
having max(sum(sal))

Swathi

• Nov 22nd, 2006

 

To Get the Max sal of each department

select max(sal),deptno from emp where sal in(select max(sal) from emp group by deptno) group by deptno;

Abhijit AUddy

• Dec 18th, 2006

 

select * from (select max(sal),deptno from emp group by deptno) where rownum <=1;

Manish Agarwal

• Dec 18th, 2006

 

Executed and tested in Oracle 8i :-

select * from (
select e.dept, sum(e.sal) esal
from emp e, dept d
where e.deptid=d.deptid
group by e.deptid
order by sum(e.sal) desc
) where rownum = 1;

Sai Srinivas Vartha

• Dec 20th, 2006

 

select deptno,sal1 from (select sum(sal) sal1,deptno from scott.emp group by deptno order by sal1 desc)  where rownum <=1

Sagar

• Jan 2nd, 2007

 

Hi,

The following query gives the reuslt for your requirement.

select * from (select deptno,max(sal) sal from emp where sal is not null

group by deptno

order by sal desc) where rownum<=1

Thanks

Sagar.S

sreenivas

• Mar 13th, 2007

 

Hai I am sreenivas from AP

to get the max sal ,deptno try  the following query

SELECT deptno,SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal)= (SELECT MAX(SUM(sal)) FROM emp GROUP BY deptno)

bharadh

• Oct 22nd, 2007

 

select  deptno,sum(sal) from emp where depno in(select deptno from emp where sal)=(select max(sal) from emp group by deptno)

vipsforever

 Profile Answers by vipsforever 

• Feb 8th, 2008

 

select department_id, m.salary from employees m where m.salary =(select max(salary) from employees where salary in( select max(salary) from employees group by department_id));