Lynn Pettis (9/25/2012)

---

Abu Dina (9/25/2012)

---

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.

I can only write code based on what you provided. Based on the sample data and expected results, what I saw was the max id being retained. With the new data, what are the rules for determining what ID is used? Also, you posted additional data but not additional expected results.