Re: SQL Question - Ranges of Sequences

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 26 Jul 1999 13:55:18 GMT
Message-ID: <7nhpc6$ssi$1@news.seed.net.tw>

Eddie <edawad_at_hotmail.com> wrote in message news:7ne3bt$73l$1_at_nnrp1.deja.com...
> 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 think it could be a candidate for a SQL puzzle. I have riddled it.

SQLWKS> select id-level+1 as "from",
     2>        min(id)+max(level)-1 as "to",
     3>        description,
     4>        max(level) as "count"
     5>   from test_table m
     6>   start with not exists (
     7>     select * from test_table
     8>       where id=m.id-1 and description=m.description)
     9>   connect by prior id=id-1
    10>     and prior description=description
    11>   group by id-level, description
    12>
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

6 rows selected. Received on Mon Jul 26 1999 - 08:55:18 CDT