Great article. It got me thinking, which first thing on a Monday morning is usually impossible! 😀

I rewrote the CASE statement, though I haven't extensively tested this - found a bug so I'll remove it for now.

And added more than 1 group to test:

-- create the sequence test table

create table dbo.ToDoList

(

todoID int identity(1,1) not null primary key,

PersonId int not null,

todoText nvarchar(200) not null

)

go

-- populate the sequence test table

insert into dbo.ToDoList(PersonId, todoText)

select 1, 'Task 0' union all

select 1, 'Task 1' union all

select 1, 'Task 2' union all

select 1, 'Task 3' union all

select 1, 'Task 4' union all

select 1, 'Task 5' union all

select 1, 'Task 6' union all

select 1, 'Task 7' union all

select 1, 'Task 8' union all

select 1, 'Task 9'

go

insert into dbo.ToDoList(PersonId, todoText)

select 2, 'Task 0' union all

select 2, 'Task 1' union all

select 2, 'Task 2' union all

select 2, 'Task 3' union all

select 2, 'Task 4'

go

SELECT * FROM dbo.ToDoList

go

alter table dbo.ToDoList

add todoSequence smallint not null default(0)

GO

-- now add the initial sequence

update dbo.ToDoList set

todoSequence = n_seq.newSequence

from dbo.ToDoList tdl

inner join

(

select ROW_NUMBER() over (partition by personID order by personID, todoID) - 1 as newSequence,

todoID,

personID

from dbo.ToDoList

) n_seq

on tdl.todoID = n_seq.todoID AND tdl.personid = n_seq.personid

GO

SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence

GO

-- move Line 8 to sequence number 2

exec dbo.[setTodoSequence]

@personID = 1,

@todoID = 9,

@todoSequence = 2

GO

SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence

GO

exec [dbo].[setTodoSequence]

@personID = 1,

@todoID = 1,

@todoSequence = 9

go

SELECT * FROM dbo.ToDoList ORDER BY personid, todoSequence

GO

DROP TABLE dbo.ToDoList

Appreciate the article and the new tool in my arsenal.