Re: Returning Range of values where gaps exist

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 26 Apr 2003 08:26:54 GMT
Message-ID: <b8dfsd$8trf6$1@ID-82536.news.dfncis.de>

> Thanks for the response Rene, I should have been more specific in the
> data at the first column was the row number.
>
> Sorry about that. I pasted the data from PL/SQL and forgot to remove
> the row number as the first column.
>
>
> It seems that would be rather important as it was used as the sequence
> in your script. Is there a way to modify your script to make it fit
> without this data?

Not sure if this is what you mean:

create table t_ (val number, reader char(3));

insert into t_ values ( 51, 'JWC');
insert into t_ values ( 52, 'JWC');
insert into t_ values ( 53, 'JWC');
insert into t_ values ( 54, 'JWC');
insert into t_ values ( 55, 'JWC');
insert into t_ values (101, 'JWC');
insert into t_ values (102, 'JWC');
insert into t_ values (103, 'JWC');
insert into t_ values (104, 'JWC');
insert into t_ values (105, 'JWC');
insert into t_ values (106, 'JWC');
insert into t_ values (107, 'JWC');

select start_, end_, reader from (
  select
    first_value(val) over (partition by diff) start_,     last_value(val) over (partition by diff) end_,     reader,
    row_number() over (partition by diff order by val) r   from (
    select

      row_number() over(order by val) -
      val diff,
      reader,
      val
    from
      t_

  )
) where r=1
order by start_;

drop table t_;

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Sat Apr 26 2003 - 03:26:54 CDT