get number of consecutive numbers

SQL SERVER 2000
Hello
I have a table with a field like this
4
5
6
10
11
15
I want to get a result set of consecutive numbers like this (start range -
end range)
4 6
10 11
15 15
Thanks in advance for your help
--
GilI'm not a big fan of converting rows to columns.
Someone might jump in and give you that solution but in the meantime, try
this:
create table #test (num int)
insert into #test values (4)
insert into #test values (5)
insert into #test values (6)
insert into #test values (10)
insert into #test values (11)
insert into #test values (15)
select t.num from #test t where not exists(select * from #test where num =
t.num-1)
Union all
select t.num from #test t where not exists(select * from #test where num =
t.num+1)
order by num
drop table #test
This will return:
4
6
10
11
15
15
Loop through the recordset in your client application to build your start
and end ranges.
Test this solution first though.
"Gil" <Gil@.discussions.microsoft.com> wrote in message
news:95EED79E-9614-44DD-A808-A4F54D186BC6@.microsoft.com...
> SQL SERVER 2000
> Hello
> I have a table with a field like this
> 4
> 5
> 6
> 10
> 11
> 15
> I want to get a result set of consecutive numbers like this (start range -
> end range)
> 4 6
> 10 11
> 15 15
> Thanks in advance for your help
> --
> Gil|||HI
Try this approch ...
SELECT id,
(SELECT TOP 1 id FROM Table1 CT Where CT.id > MT.id)
FROM TAble1 MT
Thanks
___________
"Gil" wrote:

> SQL SERVER 2000
> Hello
> I have a table with a field like this
> 4
> 5
> 6
> 10
> 11
> 15
> I want to get a result set of consecutive numbers like this (start range -
> end range)
> 4 6
> 10 11
> 15 15
> Thanks in advance for your help
> --
> Gil|||create table #test (num int)
insert into #test values (4)
insert into #test values (5)
insert into #test values (6)
insert into #test values (10)
insert into #test values (11)
insert into #test values (15)
select max(n_from), n_to from
(select t.num n_from from #test t where not exists(select * from #test
where num =
t.num-1))starts
join
(select t.num n_to from #test t where not exists(select * from #test
where num =
t.num+1) )ends
on n_from < n_to
group by n_to
order by n_to
n_to
-- --
4 6
10 11
10 15
(3 row(s) affected)
drop table #test|||"Akbar khan is a Senior Database develope"
< AkbarkhanisaSeniorDatabasedevelope@.discu
ssions.microsoft.com> wrote in
message news:2E6ED1EA-673C-46C9-9FBC-4F54D3A86E8E@.microsoft.com...
> HI
> Try this approch ...
> SELECT id,
> (SELECT TOP 1 id FROM Table1 CT Where CT.id > MT.id)
> FROM TAble1 MT
> Thanks
Returns:
4 5
5 6
6 10
10 11
11 15
15 NULL
This is not what the poster wanted.|||"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1127410743.315339.181750@.g43g2000cwa.googlegroups.com...
> create table #test (num int)
> insert into #test values (4)
> insert into #test values (5)
> insert into #test values (6)
> insert into #test values (10)
> insert into #test values (11)
> insert into #test values (15)
> select max(n_from), n_to from
> (select t.num n_from from #test t where not exists(select * from #test
> where num =
> t.num-1))starts
> join
> (select t.num n_to from #test t where not exists(select * from #test
> where num =
> t.num+1) )ends
> on n_from < n_to
> group by n_to
> order by n_to
>
> n_to
> -- --
> 4 6
> 10 11
> 10 15
> (3 row(s) affected)
> drop table #test
>
Not what the poster wanted:
4 6
10 11
15 15|||yeah right, should be
on n_from <= n_to
instead of
on n_from < n_to
here you go
select max(n_from), n_to from
(select t.num n_from from #test t where not exists(select * from #test
where num =
t.num-1))starts
join
(select t.num n_to from #test t where not exists(select * from #test
where num =
t.num+1) )ends
on n_from <= n_to
group by n_to
order by n_to
n_to
-- --
4 6
10 11
15 15
(3 row(s) affected)|||Here's one without a union:
select t1.num as [start],
(select top 1 t3.num from #test t3 where t3.num >= t1.num and not
exists(select t4.num from #test t4 where t4.num = t3.num+1) order by t3.num)
as [end]
from #test t1 where not exists(select t2.num from #test t2 where t2.num =
t1.num-1)
order by [start]
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1127413636.304427.70620@.g44g2000cwa.googlegroups.com...
> yeah right, should be
> on n_from <= n_to
> instead of
> on n_from < n_to
> here you go
> select max(n_from), n_to from
> (select t.num n_from from #test t where not exists(select * from #test
> where num =
> t.num-1))starts
> join
> (select t.num n_to from #test t where not exists(select * from #test
> where num =
> t.num+1) )ends
> on n_from <= n_to
> group by n_to
> order by n_to
> n_to
> -- --
> 4 6
> 10 11
> 15 15
> (3 row(s) affected)
>