How to reorder records (SortOrder column) after deleting a record in SQL Server?

Question

I was wondering what the most efficient way is to reorder my records after I delete a record. Basically I have something like (ItemID, SortOrder). So when I delete an item, the SortOrder column will no longer be incremental. I want to re number sort order so that the numbers are incremental again.

I was thinking maybe I could do this with the RANK feature...but I'm not quite sure how to do it.

Answer 1

Assuming this is a periodic tidy up then the following might work to "fix" all gaps.

SELECT * 
INTO #TEST
FROM
(

SELECT 1 AS PK, 1 AS ItemID, 1 AS SortOrder
UNION 
SELECT 2 AS PK,  1 AS ItemID, 3 AS SortOrder
UNION 
SELECT 3 AS PK,  1 AS ItemID, 4 AS SortOrder
UNION
SELECT 4 AS PK, 2 AS ItemID, 2 AS SortOrder
UNION 
SELECT 5 AS PK,  2 AS ItemID, 3 AS SortOrder
UNION 
SELECT 6 AS PK, 2 AS ItemID, 4 AS SortOrder
)H
;
WITH Tidied As
(
SELECT PK, ItemId, ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY SortOrder) AS NewSortOrder
FROM #TEST )


UPDATE    #TEST
SET     #TEST.SortOrder  = NewSortOrder       
FROM         #TEST INNER JOIN
                      Tidied ON #TEST.PK = Tidied.PK

SELECT * FROM #TEST

Answer 2

If you're using something that supports T-SQL (i.e. not Compact Edition), then this should work.

Assuming your table is named Item and the orders are currently incremental (no gaps),

declare @SortOrder int

select @SortOrder = SortOrder from Item where ItemID = @ItemID

update Item set SortOrder = SortOrder - 1 where SortOrder > @SortOrder

delete Item where ItemID = @ItemID