RE: query problem

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 28 Sep 2004 11:56:28 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKEEMCFGAA.mwf@rsiz.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Srinivas T Sent: Tuesday, September 28, 2004 5:37 AM To: oracle-l_at_freelists.org
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 Received on Tue Sep 28 2004 - 10:52:57 CDT