Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Returning Range of values where gaps exist

Re: Returning Range of values where gaps exist

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 24 Apr 2003 18:16:53 GMT
Message-ID: <b899ml$7pdd1$2@ID-82536.news.dfncis.de>

> I have data that looks like this.
>
>
> 51 200201049 051 JWC
> 52 200201049 052 JWC
> 53 200201049 053 JWC
> 54 200201049 054 JWC
> 55 200201049 055 JWC
> 56 200201049 101 JWC
> 57 200201049 102 JWC
> 58 200201049 103 JWC
> 59 200201049 104 JWC
> 60 200201049 105 JWC
> 61 200201049 106 JWC
> 62 200201049 107 JWC
>
> What I want to do if possible is use SQL to returns two rows that
> looks like this.
>
> Sample ID Start End Reader
> 200201049 051 055 JWC
> 200201049 101 107 JWC
>
> The data would return two rows displaying the consecutive range
> creating a new row where a gap exists.
>
> Is this possilbe?

Yes, should be:

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

insert into t_ values (51,  51, 'JWC');
insert into t_ values (52,  52, 'JWC');
insert into t_ values (53,  53, 'JWC');
insert into t_ values (54,  54, 'JWC');
insert into t_ values (55,  55, 'JWC');
insert into t_ values (56, 101, 'JWC');
insert into t_ values (57, 102, 'JWC');
insert into t_ values (58, 103, 'JWC');
insert into t_ values (59, 104, 'JWC');
insert into t_ values (60, 105, 'JWC');
insert into t_ values (61, 106, 'JWC');
insert into t_ values (62, 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 seq) r   from (
    select

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

  )
) where r=1
order by start_;

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html
Received on Thu Apr 24 2003 - 13:16:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US