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

I have updated the answer for the update, you can check that also. — PSK, Feb 05 '18 at 12:08

Yogesh Sharma · Accepted Answer · 2018-02-05T12:12:08.217

2

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

edited Feb 05 '18 at 12:12

answered Feb 05 '18 at 10:50

Yogesh Sharma

49,870
5
26
52

Thank you for the answer, but how can I use this in update method, so my [Index] column would use those values? – Taurib Feb 05 '18 at 11:51
Why would I want to use join? Statement, that I put into my original question as solution seems to work as well. – Taurib Feb 05 '18 at 12:19
1

@Taurib .. yes no need for explicitly `join` but, `join` would be guaranteed update your index with appropriate `id's` – Yogesh Sharma Feb 05 '18 at 12:23

score 2 · Answer 2 · answered Feb 05 '18 at 10:51

You dont need to add a column if you want to get those results, you can use the ROW_NUMBER function e.g.

DECLARE @table TABLE(Id INT, InvoiceRequestId INT);
INSERT INTO @table
VALUES
(2734, 620),
(2735, 620),
(2736, 621),
(2737, 622),
(2738, 622),
(2739, 622);

SELECT Id,
       InvoiceRequestId,
       ROW_NUMBER() OVER(PARTITION BY InvoiceRequestId ORDER BY Id) AS [Index]
FROM @table;

Will give you:

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

It starts the count at 1 rather than 0 but the results are the same. You can add a '-1' if you need it to be 0

If you still want to add the column then you can build a temp table using the same function and insert from there

Grantly · Answer 3 · 2018-02-05T12:19:22.297

2

You could use one of the RANK functions in SQL Server, to create a new column called NewIndex for example

Link here

SELECT ID, InvoiceRequestID, 
ROW_NUMBER() OVER (PARTITION BY InvoiceRequestID ORDER BY ID) -1 AS NewIndex
FROM SomeTable

To update the Index column you could use this (although I haven't tested this code...)

UPDATE SomeTable
SET [Index] = ROW_NUMBER() OVER (PARTITION BY InvoiceRequestID ORDER BY ID) -1

edited Feb 05 '18 at 12:19

answered Feb 05 '18 at 10:51

Grantly

2,546
2
21
31

Thank you for the response. Your solution is just to select, but how could I update [Index] to set this value? – Taurib Feb 05 '18 at 11:36
@Taurib You can use the same expression in your UPDATE statement, I will edit my answer – Grantly Feb 05 '18 at 12:18
With your update script I get `Windowed functions can only appear in the SELECT or ORDER BY clauses.` – Taurib Feb 05 '18 at 12:21

PSK · Answer 4 · 2018-02-05T12:05:35.110

Following query should work. ROW_NUMBER -1 is being done as you are looking for index to be stared from 0.

    WITH Requests as (
    select * from (values 
        (2734 , 620 ,   0)
        ,(2735,  620 ,   0)
        ,(2736,  621 ,   0)
        ,(2737 , 622 ,   0)
        ,(2738,  622 ,   0)
        ,(2739,  622 ,   0)
      ) t (Id, InvoiceRequestId  , [Index])
    )

SELECT Id, InvoiceRequestId,(ROW_NUMBER() 
    OVER(PARTITION BY  InvoiceRequestId 
    ORDER BY Id, InvoiceRequestId ASC)-1) AS Index
 FROM Requests

You will get output as following.

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

To update back you can use following query

UPDATE R
SET R.[Index] = N.[Index]

FROM Requests R
INNER JOIN
(

SELECT Id, InvoiceRequestId,(ROW_NUMBER() 
    OVER(PARTITION BY  InvoiceRequestId 
    ORDER BY Id, InvoiceRequestId ASC)-1) AS [Index]
 FROM Requests
 ) N
 ON N.Id = R.ID AND N.InvoiceRequestId= R.InvoiceRequestId

SQL Set Index (order value) to column

4 Answers4