By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename([ID] int, [Col1] varchar(1), [Col2] int);
INSERT INTO tablename([ID], [Col1], [Col2]) VALUES
(1, 'A', 0),
(2, 'B', 0),
(3, 'C', 1),
(4, 'A', 1),
(5, 'D', 0),
(6, 'A', 0),
(7, 'F', 1),
(8, 'H', 1);
8 rows affected
with
cte1 as (
select *, sum(case when col1 = 'A' and col2 = 0 then 1 else 0 end) over (order by id) grp
from tablename
),
cte2 as (
select *, min(case when col2 = 1 then id end) over (partition by grp order by id) next_id
from cte1
)
select id, col1, col2
from cte2
where (col1 = 'A' and col2 = 1) or (id = next_id)
| id | col1 | col2 |
|---|---|---|
| 3 | C | 1 |
| 4 | A | 1 |
| 7 | F | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.