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

Answer 1

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

Answer 2

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.