CODEWITHSUNDEEP
t-sqlrow-number
Emil
Emil

Reputation: 6903

row_number starting from the last number?

by using row number, I have assigned some columns by grouping them. basically query as below. 

update product set row_number= CONVERT(nvarchar(255),  (ROW_NUMBER() over (partition by product.groupID order by product.groupID)))

But now there are new products and they dont have row_number (as null). I can't reassign all row_numbers. I would like to continue assigning null ones by using the last number. how can I do it with tsql.

basically, according to the sample table below. D should get 4, G should get 3 and H should be 1.

 product row_number groupID
  A         1         1
  B         2         1
  C         3         1
  D        null       1
  E         1         2
  F         2         2
  G        null       2
  H        null       3

Upvotes: 0

Views: 93

Answers (2)

Jerrad
Jerrad

Reputation: 5290

Create a CTE that creates the row numbers, then update the product table with the results of the CTE.

;with cte(product, groupId, newRowNumber)
as(
select product, groupId, ROW_NUMBER() over (partition by product.groupID order by product.groupID, product.product)
from product 
)

update product
set row_number = newRowNumber
from cte
where cte.groupId = product.groupId 
and cte.product = product.product
and product.row_number is null

SQL Fiddle

Upvotes: 1

mikle
mikle

Reputation: 1

Try this:

with list as **(select ID, ROW_NUMBER() over (partition by groupid order by groupid) as rn from product) update product set dbo.product.row_number= CONVERT(nvarchar(255), list.rn) from list inner join dbo.product on list.ID = dbo.product.ID**

Upvotes: 0

Related Questions