RE: query for top customer

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;

---
scott_at_ORCLSVR.QTEL.COM.QA> 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
scott_at_ORCLSVR.QTEL.COM.QA> /
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:Kevin.Thomas_at_calanais.com]
> 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: oradba_at_tubes.tatasteel.com
> 
> 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: ListGuru_at_fatcity.com (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: Kevin.Thomas_at_calanais.com
> 
> 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: ListGuru_at_fatcity.com (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: NIRMALK_at_qtel.com.qa

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: ListGuru_at_fatcity.com (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: Kevin.Thomas_at_calanais.com

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: ListGuru_at_fatcity.com (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).