counting the maximum sequence of consecutive value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

counting the maximum sequence of consecutive value

Author

Topic

andrea640
Starting Member

5 Posts

Posted - 2011-08-06 : 21:39:49

Hello everyone.
I have a table-database consists of two columns:

Id* Value
1* 0
2* 0
3* 1
4* 1
5* 1
6* 0
7* 0
8* 0
9* 1
10* 0
11* 1
12* 0

According to you, wanting me to return as a select value
The maximum number of consecutive zero (in the table the maximum number of consecutive zeros is 3) and the maximum number of consecutive 1 (in this case is 3), how to act?

Thanks to anyone who wishes to respond.
frankie

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 02:33:18

Found a similar question and answer on another forum posted by Brian Swan. Made a couple modifications. I believe this should get you what you want:

declare @table table (id int primary key identity not null, intvalue tinyint)

insert into @table (intvalue)
values (0), (0), (1), (1), (1), (0), (0), (0), (1), (0), (1), (0)

--get the number of rows
declare @row_count as int;
set @row_count= (select COUNT(*) from @table);

--a counter to be used in the while loop
declare @count as int;
set @count = 1;

--keep track of the highest number of consecutive zeros
declare @high_zero_count as int;
set @high_zero_count=0;

--keep track of consecutive zeros
declare @zero_count as int;
set @zero_count=0;

--set starting date
declare @id int;
set @id = 1

while @count <= @row_count
begin
if (select intvalue from @table where id = @id) = 1
begin
set @zero_count = 0;
end
else
begin
set @zero_count = @zero_count + 1;
end
if (@zero_count > @high_zero_count)
begin
set @high_zero_count = @zero_count;
end
--advance id by one
set @id=@id+1;

--increment the counter
set @count = @count + 1;
end;

select @high_zero_count;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-07 : 03:41:37

[code]
; with
grp as
(
select *,
grp = id - row_number() over (order by value, id)
from tbl -- yourtable
)
select top 1 count(*)
from grp
where value = 0 -- change to 1 for finding consecutive 1
group by grp
order by count(*) desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

andrea640
Starting Member

5 Posts

Posted - 2011-08-07 : 04:32:50

Thanks for the replies: much.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 11:04:34

Sure thing..Khtan's example is WAY more practical...that's actually going to help me with something else I need to do.

Khtan, any idea how you would handle it if you wanted to group by each value to return the max rows of each, not just 0?

Doing a group by intvalue skews the results. So say you wanted to return

intvalue MaxNumberRows
0 3
1 3

andrea640
Starting Member

5 Posts

Posted - 2011-08-09 : 18:24:29

Thanks: marvellous

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-09 : 20:39:54

[code]
; with
grp as
(
select *,
grp = id - row_number() over (order by value, id)
from tbl
),
grp2 as
(
select value, cnt = count(*)
from grp
group by grp, value
)
select value, max(cnt)
from grp2
group by value
[/code]

KH
[spoiler]Time is always against us[/spoiler]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-14 : 15:44:42

Ah, very cool...I'm following :) thanks khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources