Re: SQL Question - Ranges of Sequences

From: Eddie <edawad_at_hotmail.com>
Date: Mon, 26 Jul 1999 18:26:01 GMT
Message-ID: <7ni97g$pfm$1@nnrp1.deja.com>

In article <379C6A93.63D9EC08_at_jhuapl.edu.nospam>,   Chris Colclough <chris.colclough_at_jhuapl.edu.nospam> wrote:
> 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.
>
>

Thank you for taking the time to solve it, but your solution does give the desired results in this example:

---

CREATE TABLE test_table
 (

  id                         NUMBER,
  description                VARCHAR2(20)

 )
INSERT INTO test_table
VALUES
(1,'D1')
/
INSERT INTO test_table
VALUES
(2,'D1')
/
INSERT INTO test_table
VALUES
(3,'D1')
/
INSERT INTO test_table
VALUES
(4,'D1')
/
INSERT INTO test_table
VALUES
(7,'D1')
/
INSERT INTO test_table
VALUES
(8,'D1')
/
INSERT INTO test_table
VALUES
(9,'D2')
/
INSERT INTO test_table
VALUES
(20,'D2')
/
INSERT INTO test_table
VALUES
(21,'D3')
/
INSERT INTO test_table
VALUES
(23,'D3')
/
INSERT INTO test_table
VALUES
(24,'D3')
/
INSERT INTO test_table
VALUES
(25,'D3')
/

---

using your select statement the result is: BID,AID,DESCRIPTION,MAX(A.ID)-MIN(B.ID)+1

1   8   D1          8 -------------------this is not the wanted result
9   9   D2          1
20  20  D2          1
21  21  D3          1
23  25  D3          3

Where as the result I want is:
from,to,DESCRIPTION,count

1    4  D1          4
7    8  D1          2
9    9  D2          1
20   20 D2          1
21   21 D3          1
23   25 D3          3

1 to 8 should be 1 to 4 and 7 to 8 because there is a gap between 4 and 7
Fumi's select statement (in this thread) worked fine. Thanks again.
--
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:26:01 CDT