Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Returning Range of values where gaps exist
> 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_
hth
Rene Nyffenegger
-- Projektleitung und Entwicklung in Oracle/C++/C# Projekten http://www.adp-gmbh.ch/cv.htmlReceived on Thu Apr 24 2003 - 13:16:53 CDT