Mysql Counting the consecutive rows that match

Question

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

A A A B B B B A A

I want the outcome to be 3

It is easy to count the total number of A but im not sure out to out the 3 most recent only.

Here is an example of how im listing all

SELECT email,subject FROM tablename where email='[email protected]' and subject='FAIL';

---

Edit: Here is some sample data that might help. For simplicity We will just have ID and Subject and order by ID

ID Subject
1 FAIL
2 FAIL
3 FAIL
4 PASS
5 PASS
6 FAIL
7 PASS
8 FAIL
9 FAIL

The result should be either 3 or 2 depending on how you order ID

Answer 1

I loaded a SQLfiddle here: http://sqlfiddle.com/#!2/5349a/1 However, in your sample data, you had two ID=5. I made it unique. Also my SQLFiddle data doesn't match yours anymore since I changed some values to make sure it worked. Have fun with it :) (This works looking at the largest ID value for the sequence)

Try this:

SELECT COUNT(*)
FROM (
  SELECT Subject, MAX(ID) AS idlimit
  FROM t
  GROUP BY Subject
  ORDER BY MAX(ID) DESC
  LIMIT 1,1) as Temp
JOIN t
  ON Temp.idlimit < t.id