increment if not same value of next column in SQL

Question

I am trying to use the Row Number in SQL. However, it's not giving desired output.

Data :

ID   Name  Output should be
111   A     1
111   B     2
111   C     3
111   C     3
111   A     4

222   A     1
222   A     1
222   B     2 
222   C     3 
222   B     4
222   B     4

Answer 1

This is a gaps-and-islands problem. As a starter: for the question to just make sense, you need a column that defines the ordering of the rows - I assumed ordering_id. Then, I would recommend lag() to get the "previous" name, and a cumulative sum() that increases everytime the name changes in adjacent rows:

select id, name,
    sum(case when name = lag_name then 0 else 1 end) over(partition by id order by ordering_id) as rn
from (
    select t.*, lag(name) over(partition by id order by ordering_id) lag_name
    from mytable t
) t