Reverse Partition

Question

So I am working on a table like so:

Sku ProductCode Product Id
01     11011         null
02     11021         null
03     11021         null
04     11011         null
05     11031         null
06     11041         null

And I want to update the product id like so:

Sku ProductCode Product Id
01     11011         01
02     11021         02
03     11021         02
04     11011         01
05     11031         03
06     11041         04

I'm using this query:

with upd  
as  
(  
SELECT *, ROW_NUMBER() OVER (PARTITION BY [Product Code] ORDER BY [Product Code]) AS rnk  
FROM temp  
)  
UPDATE upd  
SET ProductId = rnk

Basically I want to count up only if it is different. Any ideas without using any functions or procs? Successive Statements is fine and so are more columns.

Answer 1

You want to use the dense_rank() function rather than row_number():

with upd  as  
     (SELECT *,
             dense_rank() OVER (ORDER BY [Product Code]) AS rnk  
      FROM temp  
     )  
UPDATE upd  
    SET ProductId = rnk