hey guys, i need some help... i am student and learning oracle 10g i need lil bit help, i cant solve the query... the query is "Show deptno with lowest sal having high average salary" plzz any one can helppp mee.... thanx in advance

Hi Kalir, Try this SELECT Deptno, MIN(Salary) 'Lowest' from emp where deptno in (SELECT deptno FROM (select deptno, MAX(AVG(salary)) FROM emp GROUP BY deptno )) group by deptno Thanks and Regards, Dhanapriya V L

@ vldpriya; Your solution gives error. Code (SQL): SQL> SELECT Deptno, MIN(Sal) "Lowest" FROM emp 2 WHERE deptno IN (SELECT deptno FROM (SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno )) 3 GROUP BY deptno; WHERE deptno IN (SELECT deptno FROM (SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno )) * ERROR at line 2: ORA-00937: NOT a single-GROUP GROUP FUNCTION SQL> SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno ; SELECT deptno, MAX(AVG(sal)) FROM emp GROUP BY deptno * ERROR at line 1: ORA-00937: NOT a single-GROUP GROUP FUNCTION @ Kalir, You might be expecting a query as shown below, though it is not very clear what REALLY you are trying to do. Code (SQL): SQL> SELECT DEPTNO,AVGSAL,MINSAL 2 FROM ( 3 SELECT DEPTNO,AVG(SAL) AVGSAL,MIN(SAL) MINSAL, 4 RANK() OVER ( ORDER BY AVG(SAL) DESC) RN 5 FROM SCOTT.EMP 6 GROUP BY DEPTNO) 7 WHERE RN =1 8 ; DEPTNO AVGSAL MINSAL ---------- ---------- ---------- 10 2916.66667 1300 SQL>

Hi Raj, As i studied in oracle there is a topic with nested group function. I dont know y its showing error. Kalir, Try this, SELECT top 1 Deptno, MIN(Salary) 'Lowest' ,AVG(salary) 'Avgsal' from emp group by deptno Order by Avgsal desc Please reply whether our understanding is correct. Thanks and Regards, Dhanapriya V L

Hi, select deptno from (select deptno, avg(sal),(select min(sal) from emp1) from emp1 group by deptno, sal order by avg(sal) desc) where rownum=1; Regards.....

Why the query is grouped by sal along with depno? If it is grouped with sal , SAL and AVG(SAL) will be same. Code (SQL): SELECT deptno,sal, avg(sal) FROM emp1 GROUP BY deptno, sal; here sal and avg(sal) will be same..