t-sql: dense rank that is increased at every occurence of a flag

Question

I have a table that has Parent Rows and Children Rows. The table consists of an increasing sequence, a row indicating whether it is a child or a parent and a code. I need to create ranking (an increasing number) that is increased only at every occurrence of IsParent column (values:0,1).

This is the table that I have: original table

This is the desirable result table: result table

Answer 1

DDL and Sample data would be great, however, looks like you want to use a Window Function. This isn't tested with your data, but this should give you the expected output.

SUM(IsParent) OVER (ORDER BY [Row Number]
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)