MySQL Check for 3 or more consecutive (specific) entries

database, mysql

Solution

SELECT COUNT(*) FROM `table` 
WHERE 
    id > 
        (IFNULL(
            (SELECT id 
            FROM `table` 
            WHERE `type`='LOGIN_SUCCESS' 
            ORDER BY id DESC 
            LIMIT 1),0
        )
    AND `type`='LOGIN_FAIL'

Will get the amount of fails since last success.

Problem

I have a following table: ``` +------------+-----------------------------------------------------------------------------------+ | Field | Type | +------------+-----------------------------------------------------------------------------------+ | id | int(10) unsigned | | type | enum('REGISTER','ACTIVATE','LOGIN_SUCCESS','LOGIN_FAIL','LOGOUT','LOCK','UNLOCK') | | user_id | int(10) unsigned | | mod_id | int(10) unsigned | | date | timestamp | | ip | int(10) unsigned | | user_agent | text | +------------+-----------------------------------------------------------------------------------+ ``` I am trying to determine, in the simplest possible way (preferably just using MySQL), if there are 3 or more consecutive records with `type = LOGIN_FAIL` since the last `type = LOGIN_SUCCESS` or since start of the table. For example ``` +----+---------------+---------+--------+---------------------+----+------------+ | id | type | user_id | mod_id | date | ip | user_agent | +----+---------------+---------+--------+---------------------+----+------------+ | 6 | LOGIN_SUCCESS | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | | 7 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | | 8 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | | 9 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | +----+---------------+---------+--------+---------------------+----+------------+ ``` would return `TRUE` while ``` +----+---------------+---------+--------+---------------------+----+------------+ | id | type | user_id | mod_id | date | ip | user_agent | +----+---------------+---------+--------+---------------------+----+------------+ | 6 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | | 7 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | | 8 | LOGIN_SUCCESS | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | | 9 | LOGIN_FAIL | 3 | NULL | 2012-07-21 14:08:32 | 0 | agent | +----+---------------+---------+--------+---------------------+----+------------+ ``` would return `FALSE`. Is it possible to do this with a simple query or do I need to implement this check in some script language? EDIT: I forgot to mention that this query would have to be limited to a certain user_id but I assume this wouldn't be a problem. Otherwise, or even better, would it be possible to count how much records fit this criteria (i.e. how many consecutive `type = LOGIN_FAILED` records exist since last `type=LOGIN_SUCCESS`)

Original source