RE: query problem
- From: "Charu Joshi" <joshic@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Sep 2004 17:02:39 +0530
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
