### Thread: Getting winning streak including score

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. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,934
Rep Power
379
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.
3. 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.
4. No Profile Picture
Contributing User
Devshed Intermediate (1500 - 1999 posts)

Join Date
Mar 2008
Posts
1,934
Rep Power
379
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 |```
5. 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.