mySQL - winning streak

Question

Hi I am trying to figure out a way of finding the largest winning streak for each member in my table. When the table was built, this was never in the plans to happen so is why Im seeking help on how I can achieve this.

My structure is as follows:

id  player_id   opponant_id     won     loss    timestamp 

If it is a persons game, the player id is their id. If they are being challenged by someone, their id is the opponant id and the won loss (1 or 0) is in relation to the player_id.

I want to find the greatest winning streak for each user.

Anyone have any ideas on how to do this with the current table structure.

regards

EDIT

here is some test data, where id 3 is the player in question:

id  player_id   won     loss    timestamp
1   6           0       1       2012-03-14 13:31:00
13  3           0       1       2012-03-15 13:10:40
17  3           0       1       2012-03-15 13:29:56
19  4           0       1       2012-03-15 13:37:36
51  3           1       0       2012-03-16 13:20:05
53  6           0       1       2012-03-16 13:32:38
81  3           0       1       2012-03-21 13:14:49
89  4           1       0       2012-03-21 14:01:28
91  5           0       1       2012-03-22 13:14:20

Answer 1

Give this a try. Edited to take into account loss rows

SELECT 
    d.player_id,
    MAX(d.winStreak) AS maxWinStreak
FROM (
    SELECT
        @cUser := 0,
        @winStreak := 0
) v, (

    SELECT
        player_id,
        won,
        timestamp,
        @winStreak := IF(won=1,IF(@cUser=player_id,@winStreak+1,1),0) AS winStreak,
        @cUser := player_id
    FROM (
        (
            -- Get results where player == player_id
            SELECT
                player_id,
                won,
                timestamp
            FROM matchTable
        ) UNION (
            -- Get results where player == opponent_id (loss=1 is good)
            SELECT
                opponent_id,
                loss,
                timestamp
            FROM matchtable
        )
    ) m
    ORDER BY 
        player_id ASC,
        timestamp ASC
) d
GROUP BY d.player_id

This works by selecting all win/loses and counting the win streak as it goes through. The subquery is then grouped by player_id and the max winStreak as calculated as it looped through is output per-player.

It seemed to work nicely against my test dataset anyway :)

To do this more efficiently I would restructure, i.e.

matches (
    matchID,
    winningPlayerID,
    timeStamp
)

players (
    playerID
    -- player name etc
)

matchesHasPlayers (
    matchID,
    playerID
)

Which would lead to an inner query of

SELECT
    matches.matchID,
    matchesHasPlayers.playerID,
    IF(matches.winningPlayerID=matchesHasPlayers.playerID,1,0) AS won
    matches.timestamp
FROM matches
INNER JOIN matchesHasPlayers
ORDER BY matches.timestamp

resulting in

SELECT 
    d.player_id,
    MAX(d.winStreak) AS maxWinStreak
FROM (
    SELECT
        @cUser := 0,
        @winStreak := 0
) v, (
    SELECT
        matchesHasPlayers.playerID,
        matches.timestamp,
        @winStreak := IF(matches.winningPlayerID=matchesHasPlayers.playerID,IF(@cUser=matchesHasPlayers.playerID,@winStreak+1,1),0) AS winStreak,
        @cUser := matchesHasPlayers.playerID
    FROM matches
    INNER JOIN matchesHasPlayers
    ORDER BY 
        matchesHasPlayers.playerID ASC,
        matches.timestamp ASC
) d
GROUP BY d.player_id

Answer 2

SELECT * FROM
(
    SELECT player_id, won, loss, timestamp
    FROM games
    WHERE player_id = 123
    UNION
    SELECT opponant_id as player_id, loss as won, won as loss, timestamp
    FROM games
    WHERE opponant_id = 123
)
ORDER BY timestamp

That will give you all the results for one player ordered by timestamp. Then you would need to loop those results and count winning records or else concatenate them all into a string and then use string functions to find your highest 11111 set in that string. That code will vary depending on the language you want to use, but logically those are the two choices.