title: Count Number of Consecutive Occurrence of values in Table with new partition count although repeated rows
body: <pre><code>create table #t_Jenas (Id int, Name char)
  
  insert into #t_Jenas values
  (1, 'A'),
  (2, 'A'),
  (3, 'B'),
  (4, 'B'),
  (5, 'B'),
  (6, 'B'),
  (7, 'C'),
  (8, 'B'),
  (9, 'B')
  </code></pre>
  
  <p>for row number partition by, if repeated rows, the number will continue that makes the minus column is not ascending. any idea how to make the partition number starts from 1 although repeat rows as data above?</p>
  
  <pre><code>select name,row_number() over (order by id) as cont ,row_number() over (partition by name order by id) as newcount,( row_number() over (order by id)-row_number() over (partition by name order by id)) as rowminusnewcount  --, count(*) as cnt
  from #t_Jenas
  </code></pre>
  
  <p>Actual:</p>
  
  <pre><code>name    cont    newcount    rowminusnewcount
  A   1   1   0
  A   2   2   0
  B   3   1   2
  B   4   2   2
  B   5   3   2
  B   6   4   2
  C   7   1   6
  B   8   5   3
  B   9   6   3
  </code></pre>
  
  <p>Expected:</p>
  
  <pre><code>name    cont    newcount    rowminusnewcount
  A   1   1   0
  A   2   2   0
  B   3   1   2
  B   4   2   2
  B   5   3   2
  B   6   4   2
  C   7   1   6
  B   8   1   7
  B   9   2   7
  </code></pre>