title: how to get maximum consecutive row count using sql
body: <p>I have a sql puzzle and can't figure out a proper way to do it except brute force case statement. Hope there are good ideas how to achieve this. Thanks for your thoughts. I am on sql server 2012.</p>
  
  <p>basically, I have groups of rows and each group has fixed 6 rows with value of either 1 or 0. Now, I need to insert a new row after each group and fill in the maximum <strong>consecutive row count</strong> for that group. see below:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>group_name, row_number, yes_no
  A, 1, 1
  A, 2, 0
  A, 3, 1
  A, 4, 1
  A, 5, 1
  A, 6, 0
  B, 1, 1
  B, 2, 1
  B, 3, 0
  B, 4, 1
  B, 5, 0
  B, 6, 0</code></pre>
  </div>
  </div>
  </p>
  
  <p>Now I would like the results to be:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>group_name, row_number, yes_no
  A, 1, 1
  A, 2, 0
  A, 3, 1
  A, 4, 1
  A, 5, 1
  A, 6, 0
  **A, 7, 3**
  B, 1, 1
  B, 2, 1
  B, 3, 0
  B, 4, 1
  B, 5, 0
  B, 6, 0
  **B, 7, 2**</code></pre>
  </div>
  </div>
  </p>
  
  <p>notice row_number 7 is a new row with the number of max consecutive rows of 1. any idea how to do this? Thank you!</p>