clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591563 fiddles created (45735 in the last week).

CREATE TABLE test (Id INT, ign INT, sp INT, `time` TIME); INSERT INTO test VALUES (1 , 1 , 25 , '10:00'), (2 , 1 , 22 , '10:01'), (3 , 1 , 0 , '10:02'), (4 , 1 , 0 , '10:03'), (5 , 1 , 0 , '10:04'), (6 , 1 , 0 , '10:05'), (7 , 1 , 0 , '10:06'), (8 , 1 , 8 , '10:07'), (9 , 1 , 25 , '10:08'), (10 , 1 , 22 , '10:09'), (11 , 1 , 0 , '10:10'), (12 , 1 , 0 , '10:11'), (13 , 1 , 0 , '10:12'), (14 , 1 , 0 , '10:13'), (15 , 1 , 8 , '10:14'), (16 , 1 , 10 , '10:15'); SELECT * FROM test;
Id ign sp time
1 1 25 10:00:00
2 1 22 10:01:00
3 1 0 10:02:00
4 1 0 10:03:00
5 1 0 10:04:00
6 1 0 10:05:00
7 1 0 10:06:00
8 1 8 10:07:00
9 1 25 10:08:00
10 1 22 10:09:00
11 1 0 10:10:00
12 1 0 10:11:00
13 1 0 10:12:00
14 1 0 10:13:00
15 1 8 10:14:00
16 1 10 10:15:00
 hidden batch(es)


SELECT MIN(`time`) time_from, MAX(`time`) time_till FROM ( SELECT *, @group := @group + CASE WHEN NOT sp AND @prev THEN 1 ELSE 0 END group_num, @prev:=sp FROM test, (SELECT @prev:=-1, @group:=0) variable WHERE ign = 1 ORDER BY `time` ) temp WHERE NOT sp GROUP BY group_num;
time_from time_till
10:02:00 10:06:00
10:10:00 10:13:00
 hidden batch(es)


-- subquery only SELECT *, @group := @group + CASE WHEN NOT sp AND @prev THEN 1 ELSE 0 END group_num, @prev:=sp FROM test, (SELECT @prev:=-1, @group:=0) variable WHERE ign = 1 ORDER BY `time`
Id ign sp time @prev:=-1 @group:=0 group_num @prev:=sp
1 1 25 10:00:00 -1 0 0 25
2 1 22 10:01:00 -1 0 0 22
3 1 0 10:02:00 -1 0 1 0
4 1 0 10:03:00 -1 0 1 0
5 1 0 10:04:00 -1 0 1 0
6 1 0 10:05:00 -1 0 1 0
7 1 0 10:06:00 -1 0 1 0
8 1 8 10:07:00 -1 0 1 8
9 1 25 10:08:00 -1 0 1 25
10 1 22 10:09:00 -1 0 1 22
11 1 0 10:10:00 -1 0 2 0
12 1 0 10:11:00 -1 0 2 0
13 1 0 10:12:00 -1 0 2 0
14 1 0 10:13:00 -1 0 2 0
15 1 8 10:14:00 -1 0 2 8
16 1 10 10:15:00 -1 0 2 10
 hidden batch(es)