RE: how to write this sql?

Here is a solution for you (should be very efficient if you have an
index on x, and the cardinality of x is low while the table is big).
You pass the stop rows count as an input in the sql. I have it set to 3
here:


CREATE OR REPLACE TYPE  test_tokens_rec_type as object( str_token
varchar2(100));
/
CREATE OR REPLACE TYPE  test_tokens_tab_type is table of
test_tokens_rec_type;
/

create table test_data ( c1 char, c2 number);
create index test_data_idx on test_data (c1, c2);

insert into test_data values ('A',1);
insert into test_data values ('A',2);
insert into test_data values ('A',3);
insert into test_data values ('A',4);

insert into test_data values ('B',1);
insert into test_data values ('B',2);

insert into test_data values ('C',1);

commit;

create or replace function test_data_uniq_c1_values return
test_tokens_tab_type as
m_tab test_tokens_tab_type :=3D test_tokens_tab_type();
m_c1  varchar2(100)        :=3D chr(0);
begin
 while true loop
   select --+  index(a, )=20
     min(c1) into m_c1
    from test_data a
    where c1 > m_c1
      and c1 is not null;
   if m_c1 is null=20
   then=20
     exit;
   else
     m_tab.extend;
     m_tab(m_tab.count)  :=3D test_tokens_rec_type(m_c1);
   end if;
 end loop;
 return(m_tab);
end;


 create or replace function c1_rows_cnt (p_c1 varchar2, p_stop_limit
number) return number parallel_enable deterministic as
 m_rows_cnt number :=3D 0;
 begin
   select count(*) into m_rows_cnt
    from (
     select --+ index(a, )
         rownum
       from test_data a
       where c1 =3D  p_c1
         and rownum <=3D p_stop_limit);
    return(m_rows_cnt);
 end;

=20
 select str_token c1, c1_rows_cnt(str_token, 3) m_cnt
    from table(cast(test_data_uniq_c1_values as test_tokens_tab_type)) a



C1 M_CNT
A 3
B 2
C 1

Hope it helps,

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: