RE: query problem

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Srinivas T
Sent: Tuesday, September 28, 2004 3:07 PM
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


*********************************************************
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

Other related posts: