RE: query problem

SQL> r
  1  select deptno,max_sal,
  2  (select ename from emp where sal = max_sal and rownum = 1) max_ename,
  3  min_sal,
  4  (select ename from emp where sal = min_sal and rownum = 1) min_ename
  5  from
  6* (select deptno, min(sal) min_sal, max(sal) max_sal from emp group by
deptno)

    DEPTNO    MAX_SAL MAX_ENAME     MIN_SAL MIN_ENAME
---------- ---------- ---------- ---------- ----------
        10       5000 KING             1300 MILLER
        20       3000 SCOTT             800 SMITH
        30       2850 BLAKE             950 JAMES

Seems to work fine for me, 'cept I guess you're generous with da raises and
you're overpaying Miller.

I guess I typed it in with the linewraps slightly differently. Maybe you've
got a garbage char in there
or something like that, but the syntax seems correct.

Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE    9.0.1.1.1       Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

Nothing special about the database, just the old free experimenter on my
laptop. Maybe try retyping it.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Srinivas T
Sent: Tuesday, September 28, 2004 5:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: query problem


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


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

Other related posts: