Re: SQL Question - Ranges of Sequences

From: Chris Colclough <chris.colclough_at_jhuapl.edu.nospam>
Date: Mon, 26 Jul 1999 10:02:59 -0400
Message-ID: <379C6A93.63D9EC08@jhuapl.edu.nospam>

An interesting problem....

Try:
select min(b.id) as bid,

        max(a.id) as aid,
        a.description as description,
        max(a.id) - min(b.id)+1

from test_table a, test_table b
where a.id = b.id+1
and a.description = b.description
group by a.description
union
select tt.id, tt.id, tt.description, 1
from test_table tt
where not exists

        (select b.id as bid, a.id as aid, a.description as description
        from test_table a, test_table b
        where a.id = b.id+1
        and a.description = b.description
        and b.id = tt.id
        union
        select b.id as bid, a.id as aid, a.description as description
        from test_table a, test_table b
        where a.id = b.id+1
        and a.description = b.description
        and a.id = tt.id) ;

hth
Chris

Eddie wrote:

> Hi everybody, I would like your help on this query.
> I have a table:
> Create table test_table (id number, description varchar2 (20));
> with the following rows:
> Insert into table test_table values (1,'D1');
> Insert into table test_table values (2,'D1');
> Insert into table test_table values (3,'D1');
> Insert into table test_table values (4,'D2');
> Insert into table test_table values (7,'D2');
> Insert into table test_table values (8,'D2');
> Insert into table test_table values (20,'D3');
> Insert into table test_table values (21,'D3');
> Insert into table test_table values (22,'D3');
> Insert into table test_table values (23,'D4');
> Insert into table test_table values (30,'D4');
> commit;
>
> I want the result to look like this:
>
> from to description count
> --------------------------------------
> 1 3 D1 3
> 4 4 D2 1
> 7 8 D2 2
> 20 22 D3 3
> 23 23 D4 1
> 30 30 D4 1
>
> I appreciate your help,
> Thank you
>
> --
> Eddie
> Application Developer
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Jul 26 1999 - 09:02:59 CDT