#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Getting winning streak including score


    Heya!

    I have a query-generated table that counts up the winning streak as long as the player keeps winning. When they player gets a positive score, the streak rises with 1, if he gets a negative score, the streak falls back to 0. The table looks like this:

    Code:
        +--------+------------------+--------+--------+
        | player |    timestamp     | points | streak |
        +--------+------------------+--------+--------+
        | John   | 22/11/2012 23:01 |     -2 |      0 |
        | John   | 22/11/2012 23:02 |      3 |      1 |
        | John   | 22/11/2012 23:04 |      5 |      2 |
        | John   | 22/11/2012 23:05 |     -2 |      0 |
        | John   | 22/11/2012 23:18 |     15 |      1 |
        | John   | 23/11/2012 23:20 |      5 |      2 |
        | Chris  | 27/11/2012 22:12 |     20 |      1 |
        | Chris  | 27/11/2012 22:14 |    -12 |      0 |
        | Chris  | 27/11/2012 22:17 |      4 |      1 |
        | Chris  | 27/11/2012 22:18 |     -4 |      0 |
        | Chris  | 27/11/2012 22:20 |     10 |      1 |
        | Chris  | 27/11/2012 22:21 |     20 |      2 |
        | Chris  | 27/11/2012 22:22 |     90 |      3 |
        +--------+------------------+--------+--------+
    I would like to get the players maximum streak, which is easy to get ofcourse, but I would also like to include the points that the player scored in that particular streak. So, for the above example the result would have to look like this:

    Code:
        +--------+--------+-----------+
        | player | points | maxstreak |
        +--------+--------+-----------+
        | John   |     20 |         2 |
        | Chris  |    120 |         3 |
        +--------+--------+-----------+
    Any idea's of how I could achieve this? Thanks in advance!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT x.*
      FROM my_table x
      JOIN ( SELECT player,MAX(streak) max_streak FROM my_table GROUP BY player) y
        ON y.player = x.player
       AND y.max_streak = x.streak;
    You may want to think about what should happen when John gets the same max streak twice.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by cafelatte
    Code:
    SELECT x.*
      FROM my_table x
      JOIN ( SELECT player,MAX(streak) max_streak FROM my_table GROUP BY player) y
        ON y.player = x.player
       AND y.max_streak = x.streak;
    You may want to think about what should happen when John gets the same max streak twice.
    thanks for your answer cafelatte. I don't think this will actually give me the right result. I want to get a sum of the points that are accumulated during the streak. Your solution doens't involve any points, or a sum of the points, so I don't see how this could lead to the desired output.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Can a player ever have sequential zeros?

    Code:
       
    +--------+------------------+--------+--------+
    | player |    timestamp     | points | streak |
    +--------+------------------+--------+--------+
    | John   | 22/11/2012 23:01 |     -2 |      0 |
    | John   | 22/11/2012 23:02 |      3 |      0 |
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Yes. As long as a player scores less than 0 points, it gets the '0' streak value.

IMN logo majestic logo threadwatch logo seochat tools logo