Re: SQL Question - Ranges of Sequences

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 appreciate your help,
> Thank you

Here is a better solution, with shorter SQL statement, less logical reads, and faster than the prior one. IMHO, this would be the fastest resolution. The subquery makes sure that Oracle return the result set in order of id and description. If you use an index-organized table, the subquery can be eliminated. The second trick is the GROUP BY clause. "id-rownum" identifies the continuity of the IDs.

SQL> select min(id) as "from", max(id) as "to",   2 description, count(*) as "count"   3 from (select * from test_table group by id, description)   4 group by id-rownum, description
  5 /

     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 Wed Jul 28 1999 - 00:25:43 CDT