Results 1 to 5 of 5
  1. #1
    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. #2
    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
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    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. #4
    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. #5
    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
  •