Search code examples
sqldatabaseposition

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.

Solution

  • 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