Re: SQL Question - Ranges of Sequences

In article <7nhpc6$ssi$1_at_news.seed.net.tw>,   "fumi" <fumi_at_tpts5.seed.net.tw> wrote:
>
> 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.
>
>

That's what I wanted. Thank you for your help. --
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 - 13:07:06 CDT