Count max number of consecutive occurrences of a value in SQL Server

user_8411349 0
Viewed 3672

I have a table with players, results and ID:

Player | Result | ID
---------------
An     | W      | 1
An     | W      | 1
An     | L      | 0
An     | W      | 1
An     | W      | 1
An     | W      | 1
Ph     | L      | 0
Ph     | W      | 1
Ph     | W      | 1
Ph     | L      | 0
Ph     | W      | 1

A 'W' will always have an ID of 1,

I need to create a query that will count the maximum number of consecutive 'W's for each player:

Player | MaxWinStreak
---------------------
An     | 3    
Ph     | 2

I tried to use Rows Unbounded Preceeding but i can only get it to count the maximum number of Ws in total, and not consecutively

    Select
    t2.player
    ,max(t2.cumulative_wins) As 'Max'

    From

    (   Select 
            t.Player
            ,Sum(ID) Over (Partition By t.Result,t.player 
             Order By t.GameWeek Rows Unbounded Preceding) As cumulative_wins

        From
             t  

             ) t2

    Group By
    t2.player

Is there a different approach i can take ?

gaps-and-islands sql-server sql
1 Answers

You need a column to specify the ordering. SQL tables represent unordered sets. In the below query, the ? represents this column.

You can use the difference of row numbers to get each winning streak:

select player, count(*) as numwins
from (select t.*,
             row_number() over (partition by player order by ?) as seqnum, 
             row_number() over (partition by player, result order by ?) as seqnum_r 
      from t
     ) t
where result = 'W'
group by player, (seqnum - seqnum_r);

You can then get the maximum:

select player, max(numwins)
from (select player, count(*) as numwins
      from (select t.*,
                   row_number() over (partition by player order by ?) as seqnum, 
                   row_number() over (partition by player, result order by ?) as seqnum_r 
            from t
           ) t
      where result = 'W'
      group by player, (seqnum - seqnum_r)
     ) pw
group by player;
user_11440350
Related