SQL Row Count Over Partition By

Question

As we know, over partition by increases until the group changes. When the group is changed, it starts over. How can the opposite be done? that is, if the group is not changed, the number should repeat as follows.

NAME | ROW_COUNT
 A        1
 A        1
 A        1
 B        2
 C        3
 C        3
 D        4
 E        5

Answer 1

Your scenario is of using dense_rank() as rank() doesn't maintain the sequence but just ranks the column also row_number() maintains the sequence but again in case of similar rank it assigns it a unique number

select name
    , dense_rank() over (partition by name order by name)
from table;