How to count longest streak per player

I have a mySQL poker database called TournamentXPlayer that contains a primary index called TournamentXPlayerID and TournamentID, PlayerID, Finish, and Payout. I've been looking at ways to count each players longest streak of finishing with开发者_如何学C a cash prize. Later I would like to include other things like a players personal streak (Not all player play every game but some do really well when they do play), longest winning streaks, and even longest streak without winning a prize. However at the moment I can't work out how best to count a streak at all. Can this be done?

Thanks Terry

I assumed that tournamentID is automatically incremented so it provides the chronology of the data.

This is the classic problem of the order by inside a group. For that purpose, you need to consider variables such as :

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;

In this example, the etaps are :

• sort by player then tournament
• for each player :
  • increment the streak variable if there was a payout
  • set to 0 if not
• set streak to 0 if there was a change of player. p_id!=playerID. p_id encapsuled the information of the last player considered.

When You are going to calculate streaks, You need to know the time, when a player finished the tournament. Otherwise, You will get wrong results if a players is multitabling, because, he might be playing one superstack tourney for 15 hours and finish in the money and in the meanwhile register and drop out of multiple hyperturbo tourneys. You can sort the tournaments by ID-s (relative to starting time), but You'll never get the right result, if You don't have the time, when player finished.

If we assume, that players don't multitable at all, then use the following algorithm:

1. Read all one player's tournaments.
2. Sort them by Tournament ID
3. Search for longest streak
4. Output the longest streak

Leave a comment, if You got questions, I will edit/complete my answer