I've got a mySQL poker database known as TournamentXPlayer that consists of a principal index known as TournamentXPlayerID and TournamentID, PlayerID, Finish, and Payout. I have been searching at methods to count each gamers longest streak of finishing having a cash prize. Later I must include other activities just like a gamers personal streak (Not every player play all the games however, many do very well once they do play), longest winning streaks, as well as longest streak without winning a prize. However right now I can not see how better to count a streak whatsoever. Is this done?

Thanks Terry

When You will calculate streaks, You should know time, whenever a player finished the tournament. Otherwise, You're going to get wrong results if your gamers is multitabling, because, he may be playing one superstack tourney for 15 hrs and finished within the money and in the intervening time register and give up of multiple hyperturbo tourneys. You are able to sort the competitions by ID-s (in accordance with beginning time), but You may never obtain the right result, without having time, when player finished.

When we assume, that gamers don't multitable whatsoever, then make use of the following formula:

  1. Read all one player's competitions.
  2. Sort them by Tournament ID
  3. Look for longest streak
  4. Output a long streak

Leave a comment, should you got questions, I'll edit/complete my answer

I believed that tournamentID is instantly incremented therefore it offers the chronology from the data.

This is actually the classic problem from the order by in the group. For your purpose, you have to consider variables for example :

mysql> set @p_id:=-1; set @streak:=0;
mysql> select playerID,max(streak) from (select playerID,@streak:=if(Payout=0,0,if(@p_id=playerID,@streak+1,1)) streak, @p_id:=playerID from (select playerID,TournamentID,Payout from table order by 1,2) a) a group by 1;

Within this example, the etaps are :

  • sort by player then tournament
  • for every player :
    • increment the streak variable if there is a payout
    • set to otherwise
  • set streak to if there is a big change of player. p_id!=playerID. p_id encapsuled the data from the last player considered.