title: Finding the largest group of consecutive numbers within a partition
body: <p>I have the following data ordered by player_id and match_date. I would like to find out the group of records that has the maximum number of consecutive runs (4 runs from 2014-04-03 till 2014-04-12 for 3 consecutive times)</p>
  
  <pre><code> player_id  match_date  runs
      1       2014-04-01    5
      1       2014-04-02    55       
      1       2014-04-03    4       
      1       2014-04-10    4       
      1       2014-04-12    4       
      1       2014-04-14    3       
      1       2014-04-19    4       
      1       2014-04-20    44               
      2       2014-04-01    23
      2       2014-04-02    23       
      2       2014-04-03    23       
      2       2014-04-10    23       
      2       2014-04-12    4       
      2       2014-04-14    3       
      2       2014-04-19    23       
      2       2014-04-20    1   
  </code></pre>
  
  <p>I have come up with the following SQL:</p>
  
  <pre><code>select *,row_number() over (partition by ranked.player_id,ranked.runs
  order by ranked.match_date) as R from (
  select player_id ,match_date,runs from players order by 1,2 desc )
  ranked order by ranked.player_id, match_date asc
  </code></pre>
  
  <p>But this <strong>continues</strong> the rank from the previous consecutive runs (4 runs on 2014-04-19 for Player 1 is expected to get Rank 1 but gets Rank 4 since there were 3 occurrences of the same partition already). Similarly 23 runs for Player 2 on 2014-04-19 is expected to get Rank 1 but gets Rank 5 since there were 4 occurrences of 23 runs already for this player. </p>
  
  <p>How do I <strong>reset</strong> the rank back to 1 when the value of runs changes from its previous row?</p>
  
  <p>Schema, data, SQL and the output is available on <strong><a href="http://sqlfiddle.com/#!15/10ff1/1" rel="nofollow">SQLFiddle</a></strong>.</p>