CODEWITHSUNDEEP
sqldatabaseposition
HumbleDev
HumbleDev

Reputation: 43

Update a column to the row number in a group by SQL

The current database I am working with has a table where the defined positions have gone out of order. I have the following query which can allow me to update the position to the Row Number (RN) but only for a single group of objects with the ParentId

SELECT id, ParentId, Position, Title, -1+ ROW_NUMBER() OVER (ORDER BY [Position]) as RN
FROM Objects
where ParentId = 4390

SQL results screenshot

However there are multiple ParentId Groups.

My question is how can this query be applied to the entire Object table while keeping these groupings and row number increments correct?

I know I could use a cursor to loop through a list of the ParentIds but this feels very inefficient.

Upvotes: 2

Views: 1744

Answers (1)

Gabriel Durac
Gabriel Durac

Reputation: 2760

You need to add a PARTITION BY 

SELECT id
    ,ParentId
    ,Position
    ,Title
    ,-1+ ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY [Position]) as RN
FROM Objects

to update i would use a CTE

WITH CTE AS ( 
    SELECT id
        ,ParentId
        ,Position
        ,Title
        ,-1+ ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY [Position]) as NewPosition
    FROM Objects
) 
UPDATE CTE 
SET Position = NewPosition 
WHERE Position <> NewPosition

Upvotes: 4

Related Questions