title: SQL Set Index (order value) to column
body: <p><strong>Short story</strong></p>
  
  <p>I have 'Rows' table for my <code>Requests</code>. Currently, when I add new row to request, there is no way to order them, so I added <code>Index</code> column. This would enable to add new rows and order them as I wish.</p>
  
  <p><strong>What I want to do</strong></p>
  
  <p>I want to write MSSQL script that <strong>sets</strong> default values for old rows to <code>Index</code> (0, 1, 2) by <code>InvoiceRequestId</code>. This should currently be set as they are (<code>Id</code> order).</p>
  
  <p><strong>Current table</strong></p>
  
  <pre><code>Id    InvoiceRequestId  Index
  2734  620               0
  2735  620               0
  2736  621               0
  2737  622               0
  2738  622               0
  2739  622               0
  ...
  </code></pre>
  
  <p><strong>What I want to achieve</strong></p>
  
  <pre><code>Id    InvoiceRequestId  Index
  2734  620               0
  2735  620               1
  2736  621               0
  2737  622               0
  2738  622               1
  2739  622               2
  ...
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>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.</p>
  
  <p><strong>Solution</strong></p>
  
  <p>So thanks for the answers below and this topic <a href="https://stackoverflow.com/questions/13648898/sql-update-with-row-number">SQL Update with row_number()</a> , I found the following solution to work:</p>
  
  <pre><code>With IndexUpdate As
  (
      SELECT [Index],
      ROW_NUMBER() OVER (PARTITION BY InvoiceRequestID ORDER BY ID) -1 as RN
      FROM [InvoiceRequestRows]
  )
  UPDATE IndexUpdate SET [Index]=RN
  </code></pre>