Hi thomas,
You use correlated sub-query for that, it's quite simple than ur stuff:
select * from emp a where &n >= (select count(distinct sal) from emp b where
a.sal <= b.sal) order by sal desc;
---
[EMAIL PROTECTED]> ed
Wrote file afiedt.buf
1 select * from emp a where &n >= (select count(distinct sal) from emp b
where a.sal <= b.sal)
2* order by sal desc
[EMAIL PROTECTED]> /
Enter value for n: 3
old 1: select * from emp a where &n >= (select count(distinct sal) from
emp b where a.sal <= b.sal)
new 1: select * from emp a where 3 >= (select count(distinct sal) from emp
b where a.sal <= b.sal)
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
--------- ---------- --------- --------- --------- --------- ---------
---------
7839 KING PRESIDENT 17-NOV-81 5000
10
7788 SCOTT ANALYST 7566 09-DEC-82 3000
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
4 rows selected.
---
The same thing you can impose in ur case also.
REgards,
Nirmal Kumar.
> -----Original Message-----
> From: Thomas, Kevin [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, September 10, 2001 6:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: query for top customer
>
> Try this...
>
> DECLARE
> CURSOR c_sales IS
> SELECT *
> FROM sales
> ORDER BY sales desc;
> r_sales c_sales%rowtype;
> l_count NUMBER := 0;
> BEGIN
> OPEN c_sales;
> DBMS_OUTPUT.PUT_LINE( 'Customer' || ' ' || 'Sales' );
> WHILE l_count < 3 LOOP
> FETCH c_sales INTO r_sales;
> DBMS_OUTPUT.PUT_LINE( r_sales.customer || ' ' || r_sales.sales );
> l_count := l_count + 1;
> END LOOP;
> CLOSE c_sales;
> END;
> /
>
> I did it as a pl/sql block because you can't be fancy (well not in 7.3.4)
> and use ROWNUM with an ORDER BY. I was going to
> suggest just doing:
>
> SELECT *
> FROM sales
> WHERE rownum < 4
> ORDER BY sales DESC;
>
> But alas, this doesn't work...try the above, it's nasty but quick...
>
> HTH,
>
> Kev.
>
>
> __________________
>
> Kevin Thomas
> Technical Analyst
> Deregulation Services
> Calanais Ltd.
> (2nd Floor East - Weirs Building)
> Tel: 0141 568 2377
> Fax: 0141 568 2366
> http://www.calanais.com
>
>
> -----Original Message-----
> Sent: 10 September 2001 14:30
> To: Multiple recipients of list ORACLE-L
>
>
> how to write a query to find top 3 customer
> based on their sales .
> eg.
> TABLE A
> customer sales
> A100 100
> A101 200
> A102 105
> A103 109
> A104 108
>
> RESULTS should be..
> A101 200
> A103 109
> A104 108
>
> Thanks in advance
> Brajesh
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Oracle DBA
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Thomas, Kevin
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nirmal Kumar Muthu Kumaran
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
