RE: how to write this sql?

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 19 Jul 2004 19:46:50 -0400
Message-ID: <42BBD772AC30EA428B057864E203C999274485@MSGBOSCLF2WIN.DMN1.FMR.COM>

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_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
-----------------------------------------------------------------

Received on Mon Jul 19 2004 - 18:44:11 CDT