3

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.

3

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)

3
  • Thank you @RolanoMySQLDBA. I did (GIVE IT A TRY), and it worked on a small set of my real data. I will try it on the real data that has huge number of rows. May 7 '15 at 14:13
  • Make sure name is indexed on the huge table. It will help the subquery a great deal May 7 '15 at 14:24
  • Hope I am not asking too much, but would please upvote my other answer (dba.stackexchange.com/a/100485/877) where I copied and pasted the algorithm from ? May 7 '15 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.