Longest consecutive run? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Longest consecutive run?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-06 : 13:16:11

Chris writes "I need some help. My data looks like this:

Seq Flag
1 1
2 1
3 1
4 0
5 1
6 1

How can I get the number of the longest consecutive run (3 in this example)? I need to know the longest run when the flag was 1.

Thanks."

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-06 : 14:11:14

I'll get cracking on this. I think the idea would be to do a self join and search for the 0's and mark them and find the distance between them. I think this should be interesting.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-06 : 14:17:43

One pitfall to watch out for is when there is a gap in the sequence:

1 1
2 1
4 1
5 0

is the correct answer 2, 3, or 4 with this example?

Be One with the Optimizer
TG

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-06 : 14:39:24

Here is my initial attempt and it will return the longest run:
declare @start int
declare @diff int
declare @count int
select @start = 0
select @diff = 0

select @diff = case when seq-@start > @diff then seq - @start-1 else @diff end, @start = seq from #mytable where flag = 0
select @count = count(*)+1 from #mytable -- Get the count from the table and add one in case the last one is 1
select @diff = case when @count - @start-1 > @diff then @count - @start -1 else @diff end
select @diff

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-06 : 14:41:16

Mine doesn't account for TG's warning. (Didn't see that response when I posted mine.)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-07 : 09:06:15

there is a post (or article) here somewhere (nr or arnoldfribble) which deals with 'streaks' in data...this is v.similiar if not identical....search for it

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-07 : 09:10:44

quote:
Originally posted by AndrewMurphy

there is a post (or article) here somewhere (nr or arnoldfribble) which deals with 'streaks' in data...this is v.similiar if not identical....search for it

(or maybe jeff!)

http://www.sqlteam.com/item.asp?ItemID=12654

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-07 : 11:56:03

You've a better memory than me (or less beer!!)...or more of need to have used that code!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-07 : 12:05:31

>>You've a better memory than me (or less beer!!)...or more of need to have used that code!

Or: I wrote the article, that's why I remember it !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources