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 http://www.sqlservercentral.com/Forums/Topic1059319-2902-4.aspx.
I have adopted the idea and incorporated with my early solution https://jingyangli.wordpress.com/2013/06/14/custom-sequence-numbering-merge-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 sys.schemas.name = N'dbo' AND sys.tables.name = N'toDoList' )
   Begin
   Drop table ToDoList
   End
 
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)
,(64,1,'photo_taken',5)
,(65,1,'photo_approved',6)
,(55,1,'line_drawing_photo',7)
 
 
---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
	USING (
	SELECT todoID
		, ROW_NUMBER() OVER(ORDER BY 
			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 
 

Advertisements

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: https://jingyangli.wordpress.com/2013/06/17/custom-sequence-numbering-2-row_number-and-merge-solution&#8230; […]


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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