MySQL find rows

Question

Lets say 100 is a start, and 101 is a stop event. These should alternate, and i want to find out whenever they don't. Each start should have a prior stop event. Given this table:

ID  EVENT_ID
10  100  // a start event
11  101  // a stop event
12   99  // some irrelevant event
13  100  // .. and so on:
14  101
15  100
16   99  // some irrelevant event
17  100  // <-- duplicate event: bad
18  100  // <-- duplicate event: bad again.
19  101

I left out the DATETIME column.

How to find #15 ánd #17, given the fact that start should not occur twice in a row?
The result should be in the form:

EVENT_ID FROM_ID FROM_DATETIME    UPTO_ID UPTO_DATETIME
100      15      2014-01-01 14:00 17      2014-01-01 16:00

I imagine it could work like this:
- a subselect to only find events 100,101 ordered by DATETIME.
- group by EVENT_ID to find duplicates using count()
..but i doubt this can collapse the results, as group would ignore the sorting,
..and i wouldn't know how to get the two (or more!) respective DATETIME values.

Is it possible to find this in MySQL? I could take care of this in PHP, but i'd rather avoid that.

Answer 1

In my example all rows have the same datetime value out of convenience.

DROP TABLE IF EXISTS T;
CREATE TABLE T
    (`ID` int auto_increment primary key, `EVENT_ID` int, dt timestamp)
;

INSERT INTO T
    (`ID`, `EVENT_ID`)
VALUES
    (10, 100),
    (11, 101),
    (12, 99),
    (13, 100),
    (14, 101),
    (15, 100),
    (16, 99),
    (17, 100),
    (18, 100),
    (19, 101)
;

select id, event_id, dt as up_to_dt, prevgood, prevdt as from_dt from
(
select 
T.*
, if((@startstop = 100 and event_id = 101) or (@startstop = 101 and event_id = 100), 'good', 'bad') as goodbad
, @prevgood := if(if((@startstop = 100 and event_id = 101) or (@startstop = 101 and event_id = 100), 'good', 'bad') = 'bad', @prevgood, id) as prevgood
, @prevdt := if(if((@startstop = 100 and event_id = 101) or (@startstop = 101 and event_id = 100), 'good', 'bad') = 'bad', @prevdt, dt) as prevdt
, @startstop := event_id
from
T, (select @startstop:=101, @prevgood:=0, @prevdt:=0) vars
where event_id in (100, 101)
order by id
) sq 
where goodbad = 'bad';

Returns

+----+----------+---------------------+----------+---------------------+
| id | event_id | up_to_dt            | prevgood | from_dt             |
+----+----------+---------------------+----------+---------------------+
| 17 |      100 | 2014-01-20 09:12:20 |       15 | 2014-01-20 09:12:20 |
| 18 |      100 | 2014-01-20 09:12:20 |       15 | 2014-01-20 09:12:20 |
+----+----------+---------------------+----------+---------------------+