How to create Seq No for repeating rows from Select query?

I have a table called Test with some repeating rows with column called Col1. The Sequence will reset to 0 when hit A. I want to create a sequence no as Result something like below:

Col1:
A
B
B
B
A
B
B
B
B

Result:

0
1
2
3
0
1
2
3
4

— query I have used

SELECT COL1, CASE COL1 WHEN 'A' THEN '0' ELSE TO_CHAR(ROWNUM-0) END AS RESULT 
FROM TEST

Only will generate:

Result:

0
2
3
4
0
6
7
8
9

Answer

Here, col1 is ordered like this in your question so there must be some criteria based on which this order is defined. Lets say the criteria is PK column.

So I am considering that there is column PK based on which col1 is ordered as seen in your question.

You can use following query:

Select col1, 
       row_number() over (partition by sum_A order by col1) - 1 as result 
from
  (Select col1, 
          sum(case when col1 = 'A' Then 1 END) over (order by PK) as sum_A
   from test)

DB<>FIDDLE DEMO

Cheers!!