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='test@example.com' 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

So you want to find how many `A` rows are consecutive start from first table row? — Alma Do, Oct 23 '13 at 12:35
Yes. The query is to find out the number or consecutive backup failures have happened in a row. This example showing that there have been 3 failures in a row. — dgibbs, Oct 23 '13 at 12:38
Given that rows in a table have no 'order' (in the conventional sense) how do we know which one is first and which is second? — Strawberry, Oct 23 '13 at 12:38
you should show a better example of sample data, and what you would expect the output to be. — AgRizzo, Oct 23 '13 at 13:11
@dgibbs I know this is old but have you solved this? It seems that AgRizzo's solution works as expected. — Ricardo Souza, May 16 '17 at 15:04

score 4 · Answer 1 · answered Oct 23 '13 at 13:43

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

Thanks. Looks this this is what I am after. I will fully test and confirm solution later :) — dgibbs, Oct 23 '13 at 14:02

valex · Answer 2 · 2013-10-23T13:08:48.837

2

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

edited Oct 23 '13 at 13:08

answered Oct 23 '13 at 12:50

valex

23,966
7
43
60

I don't think that works - See this http://sqlfiddle.com/#!2/973a1/1: I changed the table values, yet the answer stayed 3 – AgRizzo Oct 23 '13 at 12:54
@AgRizzo I've added one more query to find sequences with more then 1 value. Thanks – valex Oct 23 '13 at 13:09
1

This is not correct. What you are actually counting is not the number of consecutive rows with the same value, but rather the number of discontinuities - i.e. how many times the value of a row is different from the value of the row before. 'ABABA' should produce an output of 1, but your script will output 5. A very clever solution... to a different problem. – Benubird Jun 27 '17 at 15:48

score 2 · Answer 3 · edited Oct 06 '19 at 12:15

2

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

edited Oct 06 '19 at 12:15

MD KAMRUL HASAN SHAHED

681
11
16

answered Aug 31 '17 at 05:59

Apoorva Srivastava

21
2

Mysql Counting the consecutive rows that match

3 Answers3

Linked