SQL – Pick first row satisfying a condition below every row satisfying another condition

Suppose I have this table, named Table

+----+------+------+
| ID | Col1 | Col2 |
+----+------+------+
|  1 | A    |    0 |
|  2 | B    |    0 |
|  3 | C    |    1 |
|  4 | A    |    1 |
|  5 | D    |    0 |
|  6 | A    |    0 |
|  7 | F    |    1 |
|  8 | H    |    1 |
+----+------+------+

I want this result:

+----+------+------+
| ID | Col1 | Col2 |
+----+------+------+
|  3 | C    |    1 |
|  4 | A    |    1 |
|  7 | F    |    1 |
+----+------+------+

That is:

• If Col1 = A and Col2 = 1, take the corresponding row
• If Col1 = A and Col2 = 0, take the first row below it where Col2 = 1

I tried something like

SELECT CASE
       WHEN t.Col2 > 0
         THEN t.Col2
       WHEN t1.Col2 > 0
         THEN t1.Col2
       WHEN t2.Col2 > 0
         THEN t2.Col2
...
FROM Table t
JOIN table t1 ON t.id - 1 = t1.id
JOIN table t2 ON t.id - 2 = t2.id
...
WHERE t.Col2 = 'A'

but it’s not quite what I was looking for. I couldn’t come up with any solution. What should I do?

Answer

Use window functions SUM() and MIN():

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)

See the demo.
Results:

> id | col1 | col2
> -: | :--- | ---:
>  3 | C    |    1
>  4 | A    |    1
>  7 | F    |    1