Match records between EventA and the previous EventB before the subsequent EventA in a specific order

Question

I have the following data (fiddle),

id	datec	event
1	2022-09-19 12:16:38	EVENTA
2	2022-09-19 12:16:38	A
3	2022-09-19 12:21:08	B
4	2022-09-19 12:21:12	EVENTD
5	2022-09-19 12:25:18	C
6	2022-09-19 12:25:18	D
7	2022-09-19 12:25:28	E
8	2022-09-19 12:25:29	F
9	2022-09-19 12:25:38	EVENTA
10	2022-09-19 12:25:39	G
11	2022-09-19 12:25:40	H
12	2022-09-19 12:25:48	I
13	2022-09-19 12:27:18	EVENTD
14	2022-09-19 12:29:08	J

I don't know how to select a value between two other values ​​but in a specific order. Only events between EVENTA and EVENTD should be returned in order.

So the result should be rows with IDs 1 to 4 and 9 to 13

Tried doing something like the following, but it gave me ids 1,4,9 and 13, omitting the content in between.

SELECT id, datec, event 
FROM table1 
WHERE event BETWEEN 'EVENTA' AND 'EVENTD';

Then I tried using this,

SELECT id, datec, event 
FROM table1 
WHERE (id BETWEEN (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' 
                   LIMIT 1) 
              AND (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' 
                   LIMIT 1,1)) 
   OR (id BETWEEN (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' 
                   LIMIT 2,1) 
              AND (SELECT id 
                   FROM table1 
                   WHERE event BETWEEN 'EVENTA' AND 'EVENTD' LIMIT 3,1));

It gives me the result but I have many rows in my table.

Could someone please guide me how to repeat this until the end because I'm sure there is a way to do it but I don't know how?

greeting,

Pierre

Answer 1

This is one way:

• Calculate running count of arming events and disarmament events, sorted by date
• Sort by the number of disarmament events, calculate the record sorting for each armed event count

At this point you should notice that when there is no EventD in our armed_event partition yet, the ranking value we generate is assumed to be 0. When the first EventD is found, its value is 1 until the next EventD is found.

So when this rank value is 0 or 1 and the event happens to be "EventD", you can filter accordingly in the WHERE clause.

WITH cte AS (
    SELECT *, SUM(`event`='EVENTA') OVER(ORDER BY datec, id) AS armed_events,
              SUM(`event`='EVENTD') OVER(ORDER BY datec, id) AS disarmed_events
    FROM Table1
), cte2 AS (
    SELECT *, DENSE_RANK() OVER(PARTITION BY armed_events ORDER BY disarmed_events) -1 AS rn
    FROM cte
)
SELECT `id`, `datec`, `event` 
FROM cte2
WHERE rn = 0 OR (rn = 1 AND `event` = 'EVENTD')
ORDER BY id

Output:

id	datec	event
1	2022-09-19 12:16:38	Activity
2	2022-09-19 12:16:38	one
3	2022-09-19 12:21:08	B
4	2022-09-19 12:21:12	event
9	2022-09-19 12:25:38	Activity
10	2022-09-19 12:25:39	G
11	2022-09-19 12:25:40	H
12	2022-09-19 12:25:48	I
13	2022-09-19 12:27:18	event