Getting winning streak including score

Dev Shed Forums Sponsor:

December 4th, 2012, 05:21 AM

Rodriguez

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 31 m 53 sec
Reputation 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!

December 4th, 2012, 07:29 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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.

December 4th, 2012, 07:44 AM

Rodriguez

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 31 m 53 sec
Reputation Power: 0

Quote:

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.

December 4th, 2012, 01:22 PM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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 |

December 4th, 2012, 01:26 PM

Rodriguez

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 31 m 53 sec
Reputation Power: 0

Yes. As long as a player scores less than 0 points, it gets the '0' streak value.

Getting winning streak including score

Developer Shed Advertisers and Affiliates