Re: SQL grouping continous records

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b2bqaq$1b3trk$1_at_ID-82536.news.dfncis.de>...
> Which Oracle Version???
>

Oracle Version is 8i.

Dear Rene,
for this small example is your resolution perfect. But i have thousands of records and the performance is down. I think the reason is the Inline-View for calculating row_last_different. I created the Inline-View as View.
Problem: If I select some Records of the view, before the Records are returned the View was completely for the whole Tabele executetd.

Can you help me?

Thx
Peter

> Starting with 8i (I believe) you have the mighty analytical functions
> at your disposal:
>
>
> set feedback off
>
> create table sum_ (col1 number, col2 char(1));
>
> insert into sum_ values ( 1,'A');
> insert into sum_ values ( 2,'A');
> insert into sum_ values ( 3,'B');
> insert into sum_ values ( 4,'A');
> insert into sum_ values ( 5,'C');
> insert into sum_ values ( 6,'C');
> insert into sum_ values ( 7,'A');
> insert into sum_ values ( 8,'A');
> insert into sum_ values ( 9,'A');
> insert into sum_ values (10,'A');
> insert into sum_ values (11,'C');
> insert into sum_ values (12,'C');
>
>
> select
> row_last_different - lag(row_last_different,1,0) over (order by col1) cnt,
> col2
> from (
> select
> col1,
> row_last_different,
> col2 from (
> select
> col1,
> decode(lead (col2,1) over(order by col1),col2,null,rank() over(order by col1)) row_last_different,
> col2
> from
> sum_
> )
> where row_last_different is not null
> );
>
> drop table sum_;
>
> results in:
>
>
> CNT C
> ---------- -
> 2 A
> 1 B
> 1 A
> 2 C
> 4 A
> 2 C
>
>
> Hth
>
> Rene Nyffenegger
Received on Fri Feb 28 2003 - 02:52:10 CST