RE: query problem

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Tue, 28 Sep 2004 17:02:39 +0530
Message-ID: <MHEAIPLKCACENJKNJIALOEBNCHAA.joshic@mahindrabt.com>

Hi,

If the version is 8i or higher, then you can try this:

SELECT DISTINCT deptno,

       MIN(sal) OVER (PARTITION BY deptno),
       FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY SAL NULLS
LAST),
       MAX(sal) OVER (PARTITION BY deptno),
       FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY SAL DESC NULLS

LAST)
FROM emp;

Regards,
Charu.

-----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 3:07 PM 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

---

Disclaimer:

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.

---

Visit us at http://www.mahindrabt.com

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2004 - 06:36:34 CDT