CODEWITHSUNDEEP
sqlsql-serversql-server-2012common-table-expressionrow-number
EkansDasnakE
EkansDasnakE

Reputation: 67

Update Column based on row_number

I created a column with default value 0 for my table. I want to update that table with the correct sequence number. Could I do that with Row_Number?

my table is:

PersonID |  Code  |  Sequence Number
---------+--------+------------
10       | D112  |    0
10       | D112  |    0
10       | D112  |    0
10       | E110  |    0
10       | E110  |    0
10       | E110  |    0
10       | D112  |    0
10       | D112  |    0
10       | D112  |    0
10       | E110  |    0
10       | E110  |    0
10       | E110  |    0

i want my table to be like this:

PersonID |  Code  |  Sequence Number
---------+--------+------------
10       | D112  |    1
10       | D112  |    1
10       | D112  |    1
10       | E110  |    2
10       | E110  |    2
10       | E110  |    2
11       | M490  |    1
11       | M490  |    1
11       | M490  |    1
11       | N550  |    2
11       | N550  |    2
11       | N550  |    2

This is the code that I have but not sure if this is correct.

WITH CTE AS (
SELECT
    t.Sequence Number,
    ROW_NUMBER() OVER (PARTITION BY t.PersonID, t.Code ORDER BY t.PersonID) AS RN
FROM Table AS t )

UPDATE CTE 
SET Sequence Number = RN

Upvotes: 0

Views: 138

Answers (1)

Matt
Matt

Reputation: 14341

DECLARE @Table AS TABLE (PersonId INt, Code CHAR(4), SequenceNumber INT)

INSERT INTO @Table VALUES (10,'D112',0),(10,'D112',0),(10,'D112',0),(10,'E110',0)
,(10,'E110',0),(10,'E110',0),(10,'D112',0),(10,'D112',0),(10,'D112',0),(10,'E110',0)
,(10,'E110',0),(10,'E110',0)


;WITH CTE AS (
SELECT
    PersonId
    ,Code
    ,SequenceNumber
    ,DENSE_RANK() OVER (PARTITION BY t.PersonID ORDER BY t.Code ) AS RN
FROM
    @Table AS t)


UPDATE cte
    SET SequenceNumber = RN

SELECT *
FROm
    @Table

You are very close just need to do your PARTITION BY and ORDER BY slightly different and then use DENSE_RANK() instead of ROW_NUMBER() to handle the ties.

Upvotes: 3

Related Questions