Oracle 21c

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

create table a
(ID, ts, value) as
select 42, timestamp '2002-03-03 00:34:42', 'A' from dual union all
select 53, timestamp '2002-03-03 00:35:46', 'B' from dual union all
select 21, timestamp '2002-03-03 00:37:02', 'A' from dual union all
select 33, timestamp '2002-03-03 03:12:56', 'C' from dual union all
select 71, timestamp '2002-03-03 10:31:14', 'B' from dual union all
select 16, timestamp '2002-03-03 17:19:21', 'A' from dual
6 rows affected

select
  a.* 
  , decode(
    /*Get ID of A*/
    value, 'A', id,
    /*else ID of previous A*/
    lag(decode(value, 'A', id, null))
      ignore nulls
      over(order by ts asc)
  ) as series
from a


ID
TS
VALUE
SERIES


42
03-MAR-02 00.34.42.000000000
A
42

53
03-MAR-02 00.35.46.000000000
B
42

21
03-MAR-02 00.37.02.000000000
A
21

33
03-MAR-02 03.12.56.000000000
C
21

71
03-MAR-02 10.31.14.000000000
B
21

16
03-MAR-02 17.19.21.000000000
A
16


select *
from a
match_recognize (
  order by ts
  measures
    /*ID of the row with value = A
     for all the rows within a match group
    */
    max(a.id) as series
  all rows per match
  pattern (a b*)
  define
    a as value = 'A',
    b as lnnvl(value = 'A')
)


TS
SERIES
ID
VALUE


03-MAR-02 00.34.42.000000000
42
42
A

03-MAR-02 00.35.46.000000000
42
53
B

03-MAR-02 00.37.02.000000000
21
21
A

03-MAR-02 03.12.56.000000000
21
33
C

03-MAR-02 10.31.14.000000000
21
71
B

03-MAR-02 17.19.21.000000000
16
16
A

ID	TS	VALUE	SERIES
42	03-MAR-02 00.34.42.000000000	A	42
53	03-MAR-02 00.35.46.000000000	B	42
21	03-MAR-02 00.37.02.000000000	A	21
33	03-MAR-02 03.12.56.000000000	C	21
71	03-MAR-02 10.31.14.000000000	B	21
16	03-MAR-02 17.19.21.000000000	A	16