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='[e-mail]' 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

You can use the following way. All we need is to count of values changes e.g. where current value not equal the previous. in this example ID is a field for order it can be id,date,....

select count(*)+1
FROM T T1
where val<>(select val from T where T.id<T1.id order by id desc LIMIT 1)

count(*)+1 because of the first sequence which has no previous value.

SQLFiddle demo

If you need to count only sequences with more than 1 values then you can use the following statement. Here HAVING count(*)>1 means that we need only sequences from 2 or more values in row. If you need 3 or more then change it to HAVING count(*)>2 and so on.

select count(*)

FROM
(
select Val,Grp
from
(
select
T1.id,T1.val,
(select max(id) from T where T.id<T1.id
                             and T.val<>T1.Val ) as Grp
FROM T T1
) T1
group by GRP HAVING count(*)>1
) T3

SQLFiddle demo

Answer 2

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

Answer 3

It is just a simple trick, if you find the first non-faildata id you can easily count the consecutive passes.

SELECT count(*) FROM 
 (SELECT ID FROM tablename 
 WHERE subject!='FAIL' LIMIT 1)
AS temp 
JOIN tablename ON temp.ID > tablename.ID