declare @activity table (

activity int,

sponsor int,

sibling int);

insert into @activity (activity,sponsor,sibling)

select 0,4,0 union

select 1,4,1 union

select 2,4,2 union

select 3,2,0 union

select 4,2,1 union

select 5,6,0 ;

select *

from @activity;

--notice for each group of sponsor,

--the sibling increments 1 for each record;

--ie max(sibling) for sponsor=4 would return 2

--get the sponsor and sibling for activities not set to sponsor 4,

--start the NewSibling at max(sibling)+1 for group sponsor=4,

--and increment each one in the resultset

select activity

, sponsor 'OldSponsor'

, sibling 'OldSibling'

, 4 'NewSponsor'

, '?' 'NewSibling' --first result should be 3, second 4, etc based on max(sibling)+x for group sponsor=4

from @activity

where sponsor <> 4

group by activity, sponsor, sibling

--how do I increment NewSibling based at Max(Sibling)+1 for the group sponsor=4?