Re: SQL grouping continous records

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 11 Feb 2003 21:35:22 GMT
Message-ID: <b2bqaq$1b3trk$1@ID-82536.news.dfncis.de>

Which Oracle Version???

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

> Hi all,
>
> I want to group continous records
> I have the following records:
>
> col1 col2
> 1 A
> 2 A
> 3 B
> 4 A
> 5 C
> 6 C
> 7 A
> 8 A
>
> The following table represent what i want:
> Count(col2) col2
> 2 A
> 1 B
> 1 A
> 2 C
> 2 A
>
> Do you know a solution in SQL???
>
> Thx
> Peter

-- 
  no sig today

Received on Tue Feb 11 2003 - 15:35:22 CST