# Mysql – Getting the count of same value in consecutive rows that are ordered by different field

MySQL

I have a table with the following structure

finals(ID, name, result)

CREATE TABLE finals (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(10) DEFAULT NULL,
result char(4) DEFAULT NULL,
PRIMARY KEY (id)
);


Where:

• ID is an auto increment key,
• name is the person's name,
• result is either Pass or Fail

# Sample data

mysql> select * from finals;
+----+------+--------+
| id | name | result |
+----+------+--------+
|  1 | John | Pass   |
|  2 | John | Fail   |
|  3 | John | Pass   |
|  4 | Kyle | Pass   |
|  5 | John | Pass   |
|  6 | Kyle | Pass   |
|  7 | Kyle | Pass   |
|  8 | Kyle | Fail   |
+----+------+--------+
8 rows in set (0.00 sec)


# Explanation

I am trying to get the names of people who have at least 3 consecutive "Pass".
So, the desired result is:
"Kyle"
Because "Kyle" has 3 cosecutive "Pass", IDs 4, 6, 7.

Note please that I don't care if this sequence is interrupted by a record of other person, but I care if it was interrupted by a record for the same person with "Fail" value in the result.

That is why "John" is not in the desired result. i.e. Although "John" has 3 "pass" in total, they are interrupted with a "Fail", so I want to exclude it from the result.

I Tried this query:

mysql> SELECT name, GROUP_CONCAT(result ORDER BY id) as result_str FROM finals GROUP BY name having result_str LIKE '%Pass,Pass,Pass%';
+------+---------------------+
| name | result_str          |
+------+---------------------+
| Kyle | Pass,Pass,Pass,Fail |
+------+---------------------+
1 row in set (0.00 sec)


It works, and this is the desired result. However, I am looking for alternative ways to

• Be more Generic
• avoid the constraints on the length of the result of GROUP_CONCAT.

Thank you in advance.

#### Best Answer

I have a solution that does not need GROUP_CONCAT

# PROPOSED SOLUTION

SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
name,
(@nametag:=MD5(CONCAT(name,':',result))),
(@x:=IF(@name=@nametag,@x+1,1)) consecutive,
(@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;


# PROPOSED SOLUTION EXECUTED

mysql> SELECT name,consecutive FROM
-> (SELECT
->     name,
->     (@nametag:=MD5(CONCAT(name,':',result))),
->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
->     (@name:=@nametag) inc
-> FROM finals ORDER BY name,id) A
-> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle |           3 |
+------+-------------+
1 row in set (0.02 sec)

mysql>


# SUBQUERY'S OUTPUT

mysql> SELECT
->     name,
->     (@nametag:=MD5(CONCAT(name,':',result))),
->     (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
->     (@name:=@nametag) inc
-> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc                              |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e         |           1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60         |           2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850         |           3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0         |           1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)

mysql>


# GIVE IT A TRY !!!

I just answered a similar question 2 days ago (Logic Value of Assignment Expression)