Custom Sequence Numbering 2 –(Row_Number() and Merge Solution)

Goldie Lesser posted a solution with Row_number function on the forum to discuss the article about custom sequencing
I have adopted the idea and incorporated with my early solution
to come up this newer version:

--DDL for table ToDoList
If Exists (  SELECT *   FROM sys.tables JOIN sys.schemas
      ON sys.tables.schema_id = sys.schemas.schema_id
   WHERE = N'dbo' AND = N'toDoList' )
   Drop table ToDoList
create table ToDoList (todoID int primary key, personId int, todoText varchar(50), todoSequence  int)
insert into ToDoList values (61,1,'colour_thumbnails',1)
,(66,1,'sent_to_print', 2)
,(63,1,'page_approved', 3)
,(60,1,'line_drawing', 4)
---Test From Here
select * from ToDoList
order by todoSequence ;
--Change parameters here!!!!!!!!!!
declare @personID int = 1,
        @todoID int = 55,
        @NewSeq int = 5

		Merge ToDoList TDL
			CASE WHEN toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END                        
		,CASE WHEN toDoID = @ToDoID AND todoSequence < @NewSeq  Then toDoSequence 
		Else toDoSequence*-1 END)		 
		AS RowNum

	FROM ToDoList WHERE PersonID=@PersonID ) as Src (todoID, RowNum)
	On TDL.todoID=Src.todoID
	WHEN Matched Then
	UPDATE SET todoSequence=Src.RowNum;

select * from ToDoList
order by todoSequence 


2 Comments on “Custom Sequence Numbering 2 –(Row_Number() and Merge Solution)”

  1. […] I have read Goldie Lesser’s comments with a Row_number() solution and you can find a newer solution with Row_number and Merge at this follow-up post:; […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s