CODEWITHSUNDEEP
sqlsql-servert-sqlrecursioncommon-table-expression
Branden
Branden

Reputation: 326

Display Upper Most Hierarchy Parent for Child

I have a table with parent child relationships and I want to, for each child, display the upper most parent. The order of the real table won't be ordered so consecutively so any kind of sorting isn't desirable. I also want to avoid doing manual joins for the max number of hierarchy levels.

Table:

Parent  Child
1       2
2       3
3       4
5       6
6       7

So for Child 4 has Parent 3, who has Parent 2, who has Parent 1. 4, 3, 2 should all have Parent 4 in the output.

Desired Output:

Parent       Child
1            2
1            3
1            4
5            6
5            7

Upvotes: 0

Views: 173

Answers (2)

HABO
HABO

Reputation: 15816

A recursive Common Table Expression (CTE) makes quick work of walking through parent/child hierarchies. This example starts with the top level parents, i.e. the rows that have no parents above them. It then adds children one level at a time while keeping track of the topmost parent.

-- Sample data.
declare @Samples as Table ( Parent Int, Child Int );
insert into @Samples ( Parent, Child ) values
  ( 1, 2 ), ( 2, 3 ), ( 3, 4 ), ( 5, 6 ), ( 6, 7 );
select * from @Samples;

-- Run the tree.
with Tree as (
  -- Start with the top level parents.
  select Parent as TopLevelParent, Child
    from @Samples as S
    -- Where the row has no parent above it.
    where not exists ( select 42 from @Samples as SS where S.Parent = SS.Child )
  union all
  -- Add the children one level at a time.
  select T.TopLevelParent, S.Child
    from Tree as T inner join
      @Samples as S on S.Parent = T.Child )
  -- Display the sorted results.
  select TopLevelParent, Child
    from Tree
    order by TopLevelParent, Child;

Upvotes: 1

Branden
Branden

Reputation: 326

;with SelectedToTopCTE as
  ( select ParentID, ChildID as Child, 1 as level
    from Table
  union all
    select d.ParentID, s.ChildID, d.level + 1
    from SelectedToTopCTE as d
      join Table s
        on d.Child = s.ParentID
  ) 
select * INTO #SelectedToTop
from SelectedToTopCTE;

SELECT Child, MAX(level) as MaxLevel INTO #UpperMostSPLT
FROM #SelectedToTop
group by Child;

SELECT A.* 
FROM #SelectedToTop A
INNER JOIN
#UpperMostSPLT B
ON A.Child = B.Child AND A.level = B.MaxLevel
ORDER BY ParentID;

Upvotes: 1

Related Questions