SQL Set Index (order value) to column

Question

Short story

I have 'Rows' table for my Requests. Currently, when I add new row to request, there is no way to order them, so I added Index column. This would enable to add new rows and order them as I wish.

What I want to do

I want to write MSSQL script that sets default values for old rows to Index (0, 1, 2) by InvoiceRequestId. This should currently be set as they are (Id order).

Current table

Id    InvoiceRequestId  Index
2734  620               0
2735  620               0
2736  621               0
2737  622               0
2738  622               0
2739  622               0
...

What I want to achieve

Id    InvoiceRequestId  Index
2734  620               0
2735  620               1
2736  621               0
2737  622               0
2738  622               1
2739  622               2
...

Edit

I see, that some started to post answers for only those rows. This should be general solution, my bad, that I didn't clarify this before.

Solution

So thanks for the answers below and this topic SQL Update with row_number() , I found the following solution to work:

With IndexUpdate As
(
    SELECT [Index],
    ROW_NUMBER() OVER (PARTITION BY InvoiceRequestID ORDER BY ID) -1 as RN
    FROM [InvoiceRequestRows]
)
UPDATE IndexUpdate SET [Index]=RN

Answer 1

You could use row_number() function

select *,
      (row_number() over(partition by InvoiceRequestId order by Id)-1)  
from table

EDIT : Use CTE for subquery resultset in order to update index with newly created index

;with cte as
(
  select *,
       (row_number() over(partition by InvoiceRequestId order by Id)-1) newindex 
  from table
)
update t set t.[Index] = c.newindex 
from cte c
join table t on t.Id = c.Id