RE: how to write this sql?

From: Norris, Gregory T [ITS] <gregory.t.norris_at_mail.sprint.com>
Date: Mon, 19 Jul 2004 15:35:27 -0500
Message-ID: <D24DD765BFEDB145B346ACDA71E1658B057B8AF9@PKDWB04C.ad.sprint.com>

This should work, although it may be less efficient than using a PL/SQL = function (since the full count occurs regardless).

	select x,=20
	       case
	          when cnt1 < 100 then cnt1
	          else 100
	       end cnt
	   from (select x, count(1) cnt1 from abc group by x)
	   order by x;

I believe the CASE statement requires 8i or above, but wouldn't swear to = that.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Guang Mei Sent: Monday, July 19, 2004 2: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:33:20 CDT