CODEWITHSUNDEEP
sqlsql-server
Taurib
Taurib

Reputation: 461

SQL Set Index (order value) to column

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

Upvotes: 4

Views: 2564

Answers (4)

Grantly
Grantly

Reputation: 2556

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

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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

Upvotes: 2

PSK
PSK

Reputation: 17943

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

Upvotes: 2

dbajtr
dbajtr

Reputation: 2044

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

Upvotes: 2

Related Questions