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: SQL Question - Ranges of Sequences

Re: SQL Question - Ranges of Sequences

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 28 Jul 1999 05:25:43 GMT
Message-ID: <7nm48n$e94$4@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 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

Original text of this message

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