title: Mysql Counting the consecutive rows that match
body: <p>I am trying to run a MySQL query but not quite sure how to do it. I want to count the number of consecutive rows that match. For example</p>
  
  <pre><code>A A A B B B B A A
  </code></pre>
  
  <p>I want the outcome to be 3</p>
  
  <p>It is easy to count the total number of <code>A</code> but im not sure out to out the 3 most recent only.</p>
  
  <p>Here is an example of how im listing all </p>
  
  <pre><code>SELECT email,subject FROM tablename where email='test@example.com' and subject='FAIL';
  </code></pre>
  
  <hr>
  
  <p><strong>Edit</strong>: Here is some sample data that might help. For simplicity We will just have ID and Subject and order by <code>ID</code></p>
  
  <pre><code>ID Subject
  1 FAIL
  2 FAIL
  3 FAIL
  4 PASS
  5 PASS
  6 FAIL
  7 PASS
  8 FAIL
  9 FAIL
  </code></pre>
  
  <p>The result should be either 3 or 2 depending on how you order <code>ID</code></p>