No way to do this without scanning the entire data since Oracle has no way to know: 1- List of buckets in the group by (distinct values of X). 2- Even if one value of X satisfied the 100 rows requirement, another value of X might still require the scan process to continue. 3- Data is not partitioned and Oracle is not data aware until it scans it all. 4- What you are asking for is procedural not relational and can be implemented by coding it in any procedural language provided the code will be data-aware or will run code to be data aware. Regards, Waleed -----Original Message----- From: Guang Mei [mailto:gmei@xxxxxxxxxx]=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@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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------