RE: query for top customer

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).