If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.

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