CODEWITHSUNDEEP
mysql
shantanuo
shantanuo

Reputation: 32218

Selecting Sequential records

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 want all the status where "success" is displayed sequentially. The ID's 1,2 and 3 will be returned. But the ID 5 will not be selected unless it is followed by another success.

Upvotes: 4

Views: 1249

Answers (4)

Nikoloff
Nikoloff

Reputation: 4160

Ok, so, I tried some solutions that were showing good results for different data set, but always had some issues. Don't know why I thought a check on the previous row is not needed... The last I can come up with is this:

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

Hope it finally solves the problem

Upvotes: 0

Nikoloff
Nikoloff

Reputation: 4160

Something is bugging me about the speed of this solution, but I think maybe 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';

Upvotes: 0

alltom
alltom

Reputation: 3252

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

The idea is to create sub-queries to fetch the 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 clause on there to limit the results to the 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 you can put the sub-queries right in a WHERE clause:

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)

Upvotes: 1

Developer
Developer

Reputation: 8646

Try this

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

Upvotes: 0

Related Questions