title: How to create Seq No for repeating rows from Select query?
body: <p>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:</p>
  
  <pre><code>Col1:
  A
  B
  B
  B
  A
  B
  B
  B
  B
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>0
  1
  2
  3
  0
  1
  2
  3
  4
  </code></pre>
  
  <p>-- query I have used</p>
  
  <pre><code>SELECT COL1, CASE COL1 WHEN 'A' THEN '0' ELSE TO_CHAR(ROWNUM-0) END AS RESULT 
  FROM TEST
  </code></pre>
  
  <p>Only will generate:</p>
  
  <p>Result:</p>
  
  <pre><code>0
  2
  3
  4
  0
  6
  7
  8
  9
  </code></pre>