How to count/increment the current number of occurances of a table column in a MS SQL select

I have a table which looks like this:

id | name| fk_something
----------------
0  | 25  | 3
1  | 25  | 2
2  | 23  | 1

and I want to add another column with a number which increments everytime row name occurs, e.g.:

id | name| fk_something| n
--------------------------
0  | 25  | 3           | 1
1  | 25  | 2           | 2
2  | 23  | 1           | 1

I’m not really sure how to achieve this. Using count() I will only get the total number of occurances of name but I want to increment n so that I have a distinct value for each row.

Answer

You want row_number() :

select t.*, row_number() over (partition by name order by id) as n
from table t;