SQL Server function to get top level parent in hierarchy

Question

I have following table (master_group) structure :

code    name                      under 

1       National Sales Manager    1
2       regional sales manager    1 
3       area sales manager        2 
4       sales manager             3

How do I get the ultimate parent of a particular row like :

code    name                      under     ultimateparent

1       National Sales Manager    1         1
2       regional sales manager    1         1
3       area sales manager        2         1
4       sales manager             3         1

@MartinDavidValentinoSiagian : i have mentioned that in the question, the second table structure. — Nitin Kabra, May 17 '15 at 12:31

score 5 · Accepted Answer · answered May 17 '15 at 12:40

With recursive cte going from top to childs:

with cte as(
  select *, code as ultimate from t where code = under
  union all
  select t.*, c.ultimate from t
  join cte c on c.code = t.under
  where t.code <> t.under
)
select * from cte

For data:

create table t (code int, name varchar(100), under int)
insert into t values
(1, 'National Sales Manager', 1),
(2, 'regional sales manager', 1),
(3, 'area sales manager', 2),
(4, 'sales manager', 3),
(5, 'a', 5),
(6, 'b', 5),
(7, 'c', 5),
(8, 'd', 7),
(9, 'e', 7),
(10, 'f', 9),
(11, 'g', 9)

it generates the output:

code    name                    under   ultimate
1       National Sales Manager  1       1
5       a                       5       5
6       b                       5       5
7       c                       5       5
8       d                       7       5
9       e                       7       5
10      f                       9       5
11      g                       9       5
2       regional sales manager  1       1
3       area sales manager      2       1
4       sales manager           3       1

Fiddle http://sqlfiddle.com/#!6/17c12e/1

Please take a look at this fiddle : http://sqlfiddle.com/#!6/f9981e/2/0. I have changed a bit data. I have 44 records but it shows only 39. I couldn't find what the issue is. — Nitin Kabra, May 22 '15 at 00:05
@NitinKabra in the end you have long long ids which are not parents nor childs of any other row. — Giorgi Nakeuri, May 22 '15 at 05:23

Gordon Linoff · Answer 2 · 2015-05-17T12:56:39.540

You can use a recursive CTE to walk the tree and then choose the highest level for each code:

with cte as (
      select mg.code, mg.name as name, mg.under as under, mg.under as parent, 1 as lev
      from master_group mg
      union all
      select mg.code, mg.name, mg.under, cte.under as parent, cte.lev + 1
      from master_group mg join
           cte
           on mg.under = cte.code
      where cte.under is not null and cte.under <> mg.code
     )
select code, name, under, parent as ultimateparent
from (select cte.*, max(lev) over (partition by cte.code) as maxlev
      from cte
     ) t
where lev = maxlev;

Here is a SQL Fiddle.

score 1 · Answer 3 · answered May 17 '15 at 12:41

I would put NULL as under (in my example ParentId) when it's the top record. With this assumption here's a solution

;

WITH Result AS
(
    SELECT Id, ParentId, Name, Id as [Top] FROM
    sample
    where  ParentId IS NULL 
    UNION ALL 
    SELECT s.Id, s.ParentId, s.Name, [Top]
    FROM sample s INNER JOIN Result R ON s.ParentId = R.Id
)

http://sqlfiddle.com/#!6/13b9d/14

score 0 · Answer 4 · answered May 17 '15 at 12:38

I suggest you to use a recursive function like this:

CREATE FUNCTION dbo.parentID (@code int)
RETURNS int AS
BEGIN
    DECLARE @ResultVar int
    SELECT @ResultVar = (SELECT under FROM master_group WHERE code = @code)
    IF @ResultVar <> @code 
    BEGIN
        SELECT @ResultVar = dbo.parentID(@ResultVar)
    END
    RETURN @ResultVar
END
GO

An use it like this:

SELECT *, 
       dbo.parentId(code) AS ultimateparent
FROM master_group

score -1 · Answer 5 · answered May 17 '15 at 20:17

I'm going to shamelessly steal the data setup from another answer and demonstrate how you'd do this with hierarchyid:

create table t (code int, name varchar(100), under int)
insert into t values
    (1, 'National Sales Manager', null),
    (2, 'regional sales manager', 1),
    (3, 'area sales manager', 2),
    (4, 'sales manager', 3),
    (5, 'a', null),
    (6, 'b', 5),
    (7, 'c', 5),
    (8, 'd', 7),
    (9, 'e', 7),
    (10, 'f', 9),
    (11, 'g', 9);

with cte as (
    select code, name, under as parentCode, code as ultimateParent, cast('/' + cast(code as varchar) + '/' as nvarchar(max)) as h
    from t
    where under is null

    union all

    select child.code, child.name, child.under as ParentCode, parent.ultimateParentCode, cast(parent.h + cast(child.code as varchar) + '/' as nvarchar(max))
    from t as child
    join cte as parent
        on child.under = parent.code
), hier as (
select code, name, parentCode, ultimateParentCode, cast(h as hierarchyid) as h
from cte
)
select code, name, parentCode, ultimateParentCode, h.ToString(), h.GetAncestor(h.GetLevel()-1).ToString()
from hier

Keep in mind, the recursive CTE need only be done once (or on data changes). The point that I'm making is that once you have a hierarchyid calculated (which you can store in row, btw), it's easy to answer the question you're posing with method calls on the hierarchyid (and possibly a join if you want to get back the progenitor's info).

SQL Server function to get top level parent in hierarchy

5 Answers5

Linked