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

Question

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 1

You want row_number() :

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

Answer 2

You may try using COUNT as an analytic function:

SELECT
    id,
    name,
    fk_something,
    COUNT(*) OVER (PARTITION BY name ORDER BY id) n
FROM yourTable
ORDER BY
    id;

Demo