Rebuild sort order after delete or insert of record

Using TSQL Ranking Function on a sort column

Where there is a sort column containing numbers from 1..n representing the order in which a list of items in the database should be displayed, you have problems managing the insert and delete of records. Insert needs the records to be moved up that are above the sort order of the item getting inserted. Delete will leave a hole in the sort order.

Here you may see how the TSQL Ranking and Partitioning functions helps out.

 

WITH TempNewsTable (NewSort,OriginalSort) AS
SELECT
ROW_NUMBER() OVER (ORDER BY SortOrder DESC),SortOrder  
FROM dbo.NewsSummaries) 
UPDATE TempNewsTable 
SET OriginalSort=NewSort

 

Here the sort column is regenerated based on the existing sort order, any gaps will be filled in.

If we want to insert a record then a tweak will leave us a gap,

WITH TempNewsTable (NewSort,OriginalSort) AS
SELECT
ROW_NUMBER() OVER (ORDER BY SortOrder DESC),SortOrder  
FROM dbo.NewsSummaries) 
UPDATE TempNewsTable 
SET OriginalSort=NewSort+1 
WHERE SortOrder >= @InsertedItemSortOrder

Where @InsertedItemSortOrder is the sort order of the item we are inserting. A whole is left for the new row to be inserted.

Update is similar again, make a hole for new item and renumber to close up the hole we leave by moving the sort order of the item we are editing.

Add comment

Loading