Lynne's solution works if we assume that the retained ID is always the maximum.

But tthe solution doesn't work with the following record set:

drop table dbo.testing

create table dbo.testing (retained int, dropped int)

insert into dbo.testing (retained, dropped)

select 767884, 157441 union all

select 1046261, 157441 union all

select 6699, 157441 union all

select 157441, 73635 union all

select 767884, 73635 union all

select 1046261, 73635 union all

select 6699, 73635 union all

select 1046261, 767884 union all

select 6699, 767884 union all

select 6699, 1046261

I will keep trying to see if I can come up with a solution but if anyone else can think of something then that'd be great!

Thanks in advance.