RE: how to write this sql?

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Mon, 19 Jul 2004 16:28:01 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C51D18F@wgdc02.wgenhq.net>

Guang,
you can try this:

select x, decode(sign(count(*)-100),1,100,0,100,count(*)) from ABC group by x;

But it stills does the count. The only way for you to count and stop at 100 is to loop (in plsql)
and check the value one by one, but that would be pretty inneficient compared to the above.

Hope that helps,

-----Original Message-----

From: Guang Mei [mailto:gmei_at_incyte.com]=20 Sent: Monday, July 19, 2004 3:50 PM
To: Oracle-L-freelists
Subject: how to write this sql?

Oracle 8173.

I have a query like this:

select x, count(1) cnt from ABC group by x;

which shows:

X CNT
---------- ----------

         1         25
         2         18
         6        156
         7        529
         8         43
         9        355

What I want is

X CNT
---------- ----------

         1         25
         2         18
         6        100
         7        100
         8         43
         9        100

This means any count > 100 will be replaced with value 100. I have no problem of displaying this. What I would like to have is to let oracle know that when the count reaches 100, stop counting for that x. This way oracle does not need to keep scan after cnt reaches 100, hopefully saving some time. (somehow it is similar to adding rownum=3D1 to let oracle stop when certain condition is met).

Is it possible to add something to the query to accomplish this?

TIA. Guang

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon Jul 19 2004 - 15:24:46 CDT