🚀 go-pugleaf

YA,

create table YA (Number int NOT NULL primary key)
go

insert into YA values (1)
insert into YA values (2)
insert into YA values (3)
insert into YA values (5)
insert into YA values (6)
insert into YA values (8)
insert into YA values (10)
insert into YA values (11)
insert into YA values (12)
insert into YA values (13)

select Start = n1,
            Size = (n2 - n1 + 1)
from (select min(y1.Number) as n1, min(y2.Number) as n2
           from YA as y1
           join YA as y2
              on (y1.Number <= y2.Number)
           where not exists (select * from YA as y3
                                          where y3.Number = y1.Number - 1)
           and     not exists (select * from YA as y4
                                           where y4.Number = y2.Number + 1)
           group by y1.Number) as y

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"YA" <ya94@hotmail.com> wrote in message news:3b13499d@news.bezeqint.net...
> I have a table on SQL Server 2000 with 1 field: Number.
> The data is like 1,2,3,6,7,10,11.
> I would like to write a SQL statement that will create a RecordSet with 2
> fields: Start, Size.
> Start: 1 , Size :3
> Start: 6 , Size :2
> Start: 10 , Size :2
> As you can see from the example I need to join all the consecutive numbers
> and how many numbers joined.
> Thanks
> ya94@hotmail.com
>
>
>