MySQL Check for three or more consecutive (specific) records

Question

I have the 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 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 the start of the table.

for instance

 +----+---------------+---------+--------+---------------------+----+------------+ | 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 | +----+---------------+---------+--------+---------------------+----+------------+ 

will return TRUE , and

 +----+---------------+---------+--------+---------------------+----+------------+ | 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 | +----+---------------+---------+--------+---------------------+----+------------+ 

will return FALSE . Is it possible to do this with a simple query, or do I need to perform this check in some script language?

EDIT: I forgot to mention that this request should be limited to a specific user_id, but I assume this will not be a problem.

Otherwise, or even better, is it possible to calculate how many records match these criteria (i.e. how many consecutive records type = LOGIN_FAILED exist since the last type=LOGIN_SUCCESS )

Answer 1

 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' 

Receive the number of failures since the last success.

Answer 2

Hope this helps you

 SELECT IF(COUNT(a.id)>=3, TRUE, FALSE) AS fresult FROM last_login AS a, ( SELECT COUNT( b.id ) AS cnt, MAX( b.id ) AS maxid FROM last_login AS b WHERE b.login_type = 'LOGIN_SUCCESS' ) AS c WHERE a.id>c.maxid OR c.cnt=0 GROUP BY a.login_type