Ooooh, tell me about it! I was kicking myself when I submitted the mailing!
I was working on a piece of PL/SQL at the time, and it was the first thing
that jumped into my head,
you can always rely on me to think of the most obscure way of doing
things...LOL.
(by the way it's Kevin not Thomas (that's my surname) don't worry about it
though) ;-)
Cheers,
Kev.
"hit any user to continue"
__________________
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 18:30
To: Multiple recipients of list ORACLE-L
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).
--
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).
