 <p>I see, you want the "unique" combinations of children, regardless of order.</p> <p>The following gets parents that are equivalent:</p> <pre><code>select m1.Parent as Parent1, m2.Parent as Parent2 from (select m.*, count(*) over (partition by Parent) as NumKids from #m m ) m1 join (select m.*, count(*) over (partition by Parent) as NumKids from #m m ) m2 on m1.ChildID = m2.ChildID group by m1.Parent, m2.Parent having count(*) = max(m1.NumKids) and max(m1.NumKids) = max(m2.NumKids); </code></pre> <p>We can now get what you want using this </p> <pre><code>with parents as ( select m1.Parent as Parent1, m2.Parent as Parent2 from (select m.*, count(*) over (partition by Parent) as NumKids from #m m ) m1 join (select m.*, count(*) over (partition by Parent) as NumKids from #m m ) m2 on m1.ChildID = m2.ChildID group by m1.Parent, m2.Parent having count(*) = max(m1.NumKids) and max(m1.NumKids) = max(m2.NumKids) ) select distinct m.* from (select min(Parent2) as theParent from parents group by Parent1 ) p join #m m on p.theParent = m.Parent; </code></pre> <p>If you want a new id instead of the old one, use:</p> <pre><code>select dense_rank() over (partition by m.Parent) as NewId, m.ChildID </code></pre> <p>in the <code>select</code>.</p>