# Thread: how to find the longest consecutive series of records

1. Registered User
Join Date
May 2007
Posts
80

## Unanswered: how to find the longest consecutive series of records

I have a situation where people need to pick football matches where they think there will be at least one goal scored. The person with the longest consecutive series wins a prize. Waheey

So i have a table with user data where i can identify a user with an ID.

Then i have a table with football matches each one having a unique ID and containing a timestamp for kickoff and a boolean indicating if the result is in and a boolean indicating if there was a goal in the match.

And then i have a table linking the user ID's to the match ID's.

The boolean result is used to eliminate any record for which the result is not yet known. The order is determined by kickoff and then team names.

So i end up with this:

userID , matchID, kickoff, home, away, result, goal

1, 2354 , 2011-05-21 17:00:00 , TeamA, TeamB, true , true
1, 1354 , 2011-05-21 19:00:00 , TeamA, TeamB, true , true
1, 3454 , 2011-05-21 19:00:00 , TeamA, TeamB, true , true
1, 4354 , 2011-05-21 20:00:00 , TeamA, TeamB, true , false
1, 3754 , 2011-05-21 20:15:00 , TeamA, TeamB, true , true
1, 6654 , 2011-05-21 21:00:00 , TeamA, TeamB, true , true
1, 2854 , 2011-05-21 21:00:00 , TeamA, TeamB, true , false

The question is what is the best/easiest/simplest/ANY way to find out the longest consecutive sequence. So in this case i'm looking to find out the first 3 records as those 3 are the longest series in this example.
Last edited by BettingSherlock; 05-21-11 at 08:19.

2. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
I have shown a solution for a similar type problem. Have a look at MySQL – counting repetitions in a specific order « IT Integrated Business Solutions

3. Registered User
Join Date
May 2007
Posts
80
Thanks, i'm getting somewhere with counting and grouping.
The problem is i have to stop at some point,
totally doing my head in now.

4. Registered User
Join Date
May 2007
Posts
80
Code:
```set @group = 0;

/* select in order of largest group */
select * from

/* count the group totals of each group per user and group */
(select userID,groupmarker,count(groupmarker) AS total
from

/* give each consecutive sequence a group number, per user */
(select *,

/* restart group number for each user */
if(userID = @user, userID, @group := 1) AS newuser1,
/* keep track of current user */
if(userID = @user, userID, @user := userID) AS newuser2,
/* while havegoal is true keep same group number */
if(ordered.havegoal is true, @group, @group := @group+1) AS groupmarker
from

/* matchdata with user selections in the correct order */
(select ur.userID,m.havegoal
from ogc_test as ur
join ogc_matches as m on m.matchID = ur.matchID
where m.matchdate >= "2011-05-01" and m.matchdate < "2011-06-01"
order by ur.userID,m.matchdate asc,m.settled desc,m.havegoal desc
) AS ordered

) AS grouped

group by userID,groupmarker
) AS totalled

order by total desc
;```
well got there,
not sure how this is going to work out with a full database,
several hundred users, each with series of 100 matches, hmmmm.

5. vaguely human
Join Date
Jun 2007
Location
London
Posts
2,527
I'm not 100% sure what you want and I haven't tested this code at all and I've had a few pints but something like this might be close:
Code:
```select userID, t1.kickoff start_of_run, count(*) num_matches
from   YourTable t1, YourTable t2
where  t1.userID = t2.userID
and t1.kickoff <= t2.kickoff
and t1.goal
and t2.goal
and not exists(
select 1
from YourTable t3
where t3.userId = t1.userID
and not t3.goal
and t3.kickoff between t1.kickoff and t2.kickoff )
group by userID, t1.kickoff
order by num_of_matches
limit 1```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•