RE: how to write this sql?
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Jul 2004 19:55:04 -0400
For some reason outlook adds some extra characters ( 3D & =3D20 ), don't
know how remove them
Waleed
-----Original Message-----
From: Khedr, Waleed=20
Sent: Monday, July 19, 2004 7:47 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: [WARNING: This message originated from outside of Fidelity] 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 :=3D3D test_tokens_tab_type();
m_c1 varchar2(100) :=3D3D chr(0);
begin
while true loop
select --+ index(a, )=3D20
min(c1) into m_c1
from test_data a
where c1 > m_c1
and c1 is not null;
if m_c1 is null=3D20
then=3D20
exit;
else
m_tab.extend;
m_tab(m_tab.count) :=3D3D 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 :=3D3D 0;
begin
select count(*) into m_rows_cnt
from (
select --+ index(a, )
rownum
from test_data a
where c1 =3D3D p_c1
and rownum <=3D3D p_stop_limit);
return(m_rows_cnt);
end;
=3D20
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]=3D20
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=3D3D1 to let oracle stop =
=3D
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
-----------------------------------------------------------------
Other related posts:
