CODEWITHSUNDEEP
mysqlcount
dgibbs
dgibbs

Reputation: 712

Mysql Counting the consecutive rows that match

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

Upvotes: 6

Views: 12492

Answers (3)

valex
valex

Reputation: 24144

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

Upvotes: 2

Apoorva Srivastava
Apoorva Srivastava

Reputation: 21

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

Upvotes: 2

AgRizzo
AgRizzo

Reputation: 5271

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

Upvotes: 4

Related Questions