CODEWITHSUNDEEP
sqlsql-server
chamara
chamara

Reputation: 12711

SQL query to update rows

I have data in a table in following format

ID  DocNumber   RevOrder
1   DOC-001     NULL
2   DOC-001     NULL
3   DOC-001     NULL
4   DOC-002     NULL
5   D0C-002     NULL
6   D0C-003     NULL

I need to update the RevOrder column in to following format

ID  DocNumber   RevOrder
1   DOC-001     3
2   DOC-001     2
3   DOC-001     1
4   DOC-002     2
5   D0C-002     1
6   D0C-003     1

Logic is: DocNumber can be duplicated and the DocNumber with the max ID gets the RevOrder = 1, next get the RevOrder = 2 etc... how can I achieve the above scenario?

Upvotes: 3

Views: 241

Answers (4)

James Hill
James Hill

Reputation: 61793

Use ROW_NUMBER() & PARTITION BY

SELECT  ID,
        DocNumber,
        ROW_NUMBER() OVER(PARTITION BY DocNumber ORDER BY DocNumber DESC) AS RevOrder
FROM    @TestTable

You can view/test a working example here: https://data.stackexchange.com/stackoverflow/q/116435/

Upvotes: 0

marc_s
marc_s

Reputation: 754258

Use this UPDATE statement based on a ROW_NUMBER() over a partition:

;WITH UpdateData AS
(
    SELECT 
       ID, DocNumber,
       ROW_NUMBER() OVER(PARTITION BY DocNumber ORDER BY ID DESC) AS 'RowNum' 
    FROM dbo.YourTable
)
UPDATE dbo.YourTable
SET RevOrder = RowNum
FROM dbo.YourTable t
INNER JOIN UpdateData ud ON t.ID = ud.ID

Upvotes: 3

Steve Fibich
Steve Fibich

Reputation: 322

I think a row_number() function with partition by clause will work

declare @temp as table(
ID int not null
,Doc_Num varchar(20) not null
,RevOrder int null
)
insert into @temp
values
(1,'DOC-001',null)
,(2,'DOC-001',null)
,(3,'DOC-001',null)
,(4,'DOC-002',null)
,(5,'DOC-002',null)
,(6,'DOC-003',null)


select * from @temp


select
t.ID
,t.Doc_Num 
,ROW_NUMBER() over(partition by t.Doc_num order by t.ID desc)
from
@temp t
order by t.ID

Upvotes: 0

Paolo Falabella
Paolo Falabella

Reputation: 25844

from SQL Server version 2005 and above, you can use 

RANK() OVER ( PARTITION BY DocNumber ORDER BY ID ASC) AS RevOrder

Upvotes: 0

Related Questions