query problem

From: Srinivas T <SrinivasT_at_KPITCummins.com>
Date: Tue, 28 Sep 2004 15:07:06 +0530
Message-ID: <4A1BE23A7B777442B60F4B4916AE0F130292FE0E@sohm.kpit.com>

HI,
Friends I need output as follows,

  DEPTNO MAX_SAL MAX_NAME MIN_SAL MIN_NAME -------- ---------- ---------- ---------- ----------

      10       5000 	KING             2600 	MILLER
      20       3000 	FORD             1100 	ADAMS
      30       2850 	BLAKE             950 	JAMES


select deptno,
           max_sal,


           (select ename from emp where sal = max_sal and rownum = 1 )

max_ename,
           min_sal,


           (select ename from emp where sal = min_sal and rownum = 1 )

min_ename
      from (
    select deptno, min(sal) min_sal, max(sal) max_sal
      from emp
     group by deptno
          )

but my quey is giving the following problem.....

    select deptno, min(sal) min_sal, max(sal) max_sal

                                         *

ERROR at line 9:
ORA-00979: not a GROUP BY expression

Kindly solve the problem......

ThankS in Advance.... Srinivas

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 28 2004 - 04:38:58 CDT