You are correct, my apologies. I did change the todoGroup column to be personID to make it more relevant but obviously forgot to change the table create script.

This will teach me not to make last minute changes! Rest assured that this will not happen again. Thank you for the prompt feedback.

Regards, James (Author)

Correct create and populate sample table SQL:

-- 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

-- add a custom sequence number

-- add the new column

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

(

selectROW_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

GO