Oracle 21c

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

create table t (id number(6), ts date, val char(1));
insert into t (id, ts, val) values (42, to_date('2002-03-03 00:34:42', 'YYYY-MM-DD HH24:MI:SS'), 'A');
1 rows affected

insert into t (id, ts, val) values (53, to_date('2002-03-03 00:35:46', 'YYYY-MM-DD HH24:MI:SS'), 'B');
1 rows affected

insert into t (id, ts, val) values (21, to_date('2002-03-03 00:37:02', 'YYYY-MM-DD HH24:MI:SS'), 'A');
1 rows affected

insert into t (id, ts, val) values (33, to_date('2002-03-03 03:12:56', 'YYYY-MM-DD HH24:MI:SS'), 'C');
1 rows affected

insert into t (id, ts, val) values (71, to_date('2002-03-03 10:31:14', 'YYYY-MM-DD HH24:MI:SS'), 'B');
1 rows affected

insert into t (id, ts, val) values (16, to_date('2002-03-03 17:19:21', 'YYYY-MM-DD HH24:MI:SS'), 'A');
1 rows affected

with
g as (
  select
    t.*, 
    case when val = 'A' then 1 else 0 end as mk,
    sum(case when val = 'A' then 1 else 0 end) over(order by ts) as grp
  from t
)
select a.*, b.id as series_id
from g a
join g b on b.grp = a.grp and b.mk = 1;


ID
TS
VAL
MK
GRP
SERIES_ID


42
03-MAR-02
A
1
1
42

53
03-MAR-02
B
0
1
42

21
03-MAR-02
A
1
2
21

33
03-MAR-02
C
0
2
21

71
03-MAR-02
B
0
2
21

16
03-MAR-02
A
1
3
16

ID	TS	VAL	MK	GRP	SERIES_ID
42	03-MAR-02	A	1	1	42
53	03-MAR-02	B	0	1	42
21	03-MAR-02	A	1	2	21
33	03-MAR-02	C	0	2	21
71	03-MAR-02	B	0	2	21
16	03-MAR-02	A	1	3	16