Sequential Record Selection

Question

CREATE TABLE `sequence` (`id` int(11) NOT NULL auto_increment, `load_status` varchar(100) default NULL, PRIMARY KEY (`id`)); INSERT INTO `sequence` VALUES (1,'success'),(2,'success'),(3,'success'),(4,'fail'),(5,'success'); mysql>select * from sequence; +----+-------------+ | id | load_status | +----+-------------+ | 1 | success | | 2 | success | | 3 | success | | 4 | fail | | 5 | success | +----+-------------+ 5 rows in set (0.00 sec) 

I need all the status where success is displayed. Identifiers 1,2 and 3 will be returned. But identifier 5 will not be selected unless another success follows.

Answer 1

If you work with a lot of data, this method may not be feasible (I am inexperienced with large databases), but maybe this can help.

The idea is to create subqueries to extract adjacent rows:

 mysql> select id, load_status, (select load_status from test as t2 where t2.id < t1.id order by t2.id desc limit 1) as prev_load_status, (select load_status from test as t3 where t3.id > t1.id order by t3.id asc limit 1) as next_load_status from test as t1; +----+-------------+------------------+------------------+ | id | load_status | prev_load_status | next_load_status | +----+-------------+------------------+------------------+ | 1 | success | NULL | success | | 2 | success | success | success | | 3 | success | success | fail | | 4 | fail | success | success | | 5 | success | fail | NULL | +----+-------------+------------------+------------------+ 5 rows in set (0.00 sec) 

I initially threw a HAVING there to limit the results to clusters:

 mysql> select id, load_status, (select load_status from test as t2 where t2.id < t1.id order by t2.id desc limit 1) as prev_load_status, (select load_status from test as t3 where t3.id > t1.id order by t3.id asc limit 1) as next_load_status from test as t1 having load_status = prev_load_status or load_status = next_load_status; +----+-------------+------------------+------------------+ | id | load_status | prev_load_status | next_load_status | +----+-------------+------------------+------------------+ | 1 | success | NULL | success | | 2 | success | success | success | | 3 | success | success | fail | +----+-------------+------------------+------------------+ 3 rows in set (0.00 sec) 

But it turns out that you can put subqueries directly in the WHERE :

 mysql> select id, load_status from test as t1 where load_status = (select load_status from test as t2 where t2.id < t1.id order by t2.id desc limit 1) or load_status = (select load_status from test as t3 where t3.id > t1.id order by t3.id asc limit 1); +----+-------------+ | id | load_status | +----+-------------+ | 1 | success | | 2 | success | | 3 | success | +----+-------------+ 3 rows in set (0.00 sec) 

Answer 2

try it

SELECT * FROM sequence ORDER BY FIELD (load_status, 'Success', 'fail');

Answer 3

Something bothers me about the speed of this solution, but I think you can use it:

 SELECT current.id FROM sequence AS current LEFT JOIN sequence AS next ON(current.id = next.id+1) WHERE (current.load_status = 'success' AND next.load_status = 'success') OR (current.load_status = 'success' AND next.load_status = 'fail') OR current.load_status = 'success'; 

Answer 4

So, I tried some solutions that showed good results for different data sets, but there were always some problems. I don’t know why I thought that checking the previous line is not needed ... The last thing I can think of is the following:

 SELECT current.id FROM sequence AS current LEFT JOIN sequence AS next ON(next.id = (SELECT id FROM sequence WHERE id > current.id ORDER BY id DESC LIMIT 1)) LEFT JOIN sequence AS prev ON(prev.id = (SELECT id FROM sequence WHERE id < current.id ORDER BY id DESC LIMIT 1)) WHERE (current.load_status = 'success' AND next.load_status = 'success') OR (current.load_status = 'success' AND next.load_status = 'fail') OR (current.load_status = 'success' AND prev.load_status = 'success') ORDER BY id 

I hope he finally solves the problem