how to write this sql?
- From: "Guang Mei" <gmei@xxxxxxxxxx>
- To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Jul 2004 15:49:41 -0400
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=1 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
