I have a column in a mysql table that lists winners from a game.
I'm trying to find a query that will return the longest run of wins for each player.
Or a way to interpret a query with php.
In this case it would be
Kate 4,
Ed 1,
Mike 2,
Harry 1,
**Winner**
Kate
Kate
Ed
Harry
Ed
Harry
Mike
Mike
Ed
Harry
Kate
Kate
Kate
Kate
Ed
Apologies in advance as I am very new to all of this.
解决方案SELECT winner, MAX(winningStreak) FROM (
SELECT
winner,
IF(winner=@prev, @rownum:=@rownum+1, @rownum:=1) AS winningStreak,
@prev:=winner
FROM
yourTable
, (SELECT @prev:=NULL, @rownum:=1) vars
/*ORDER BY whateverDeterminesTheOrderOfTheWinners*/
)sq
GROUP BY winner
ORDER BY winningStreak DESC
You need another column which determines the order of the winners like you have listed them and adjust the outcommented part of the query. Apart from that this query works, but it would really be easier done in PHP.
See it live here.