Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE events (event_type VARCHAR2(10), tstamp DATE, event_id NUMBER);
BEGIN
INSERT INTO events VALUES('x', '01-Apr-11', 1);
INSERT INTO events VALUES('x', '02-Apr-11', 2);
INSERT INTO events VALUES('x', '03-Apr-11', 3);
INSERT INTO events VALUES('x', '04-Apr-11', 4);
INSERT INTO events VALUES('y', '06-Apr-11', 5);
INSERT INTO events VALUES('y', '07-Apr-11', 6);
INSERT INTO events VALUES('z', '08-Apr-11', 7);
INSERT INTO events VALUES('z', '09-Apr-11', 8);
commit;
END;
/

1 rows affected

SELECT * FROM (
select  * from events
  order by tstamp ASC
)
  MATCH_RECOGNIZE(
    MEASURES
      MATCH_NUMBER() AS match_number,
      classifier() as cl,
      FIRST(event_id) as first_id
    ALL ROWS PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN(e1 ANY_ROW* e2 ANY_ROWS* e3)
    DEFINE
      ANY_ROW AS TRUE,
      e1 AS event_type = 'x',
      e2 AS event_type = 'y',
      e3 AS event_type = 'z'
  )
where cl in ('E1','E2','E3')


MATCH_NUMBER
CL
FIRST_ID
EVENT_TYPE
TSTAMP
EVENT_ID


1
E1
1
x
01-APR-11
1

1
E2
1
y
07-APR-11
6

1
E3
1
z
09-APR-11
8

2
E1
2
x
02-APR-11
2

2
E2
2
y
07-APR-11
6

2
E3
2
z
09-APR-11
8

3
E1
3
x
03-APR-11
3

3
E2
3
y
07-APR-11
6

3
E3
3
z
09-APR-11
8

4
E1
4
x
04-APR-11
4

4
E2
4
y
07-APR-11
6

4
E3
4
z
09-APR-11
8

MATCH_NUMBER	CL	FIRST_ID	EVENT_TYPE	TSTAMP	EVENT_ID
1	E1	1	x	01-APR-11	1
1	E2	1	y	07-APR-11	6
1	E3	1	z	09-APR-11	8
2	E1	2	x	02-APR-11	2
2	E2	2	y	07-APR-11	6
2	E3	2	z	09-APR-11	8
3	E1	3	x	03-APR-11	3
3	E2	3	y	07-APR-11	6
3	E3	3	z	09-APR-11	8
4	E1	4	x	04-APR-11	4
4	E2	4	y	07-APR-11	6
4	E3	4	z	09-APR-11	8