How can I increment counter when the value in another column changes without sorting the column?

I have the following table

And I need to be able to increment the counter value when the Name changes in SQL

Answer

You need a column that specifies the ordering of the columns — because SQL tables represent unordered sets. If you have one, you can do use a gap-and-islands trick with the difference of row numbers:

select t.*,
       row_number() over (partition by name, seqnum - seqnum_2 order by <ordercol>) as value
from (select t.*,
             row_number() over (order by <ordercol>) as seqnum,
             row_number() over (partition by name order by <ordercol>) as seqnum_2
      from t
     ) t
order by <order col>;