sql - Finding the Ultimate manager ID -

the problem description:

i need find ultimate manager's name (or id) given table particular id.
is,for eg let:

3 has manager id 4 4 has manager id 5, 5 has manager id 109 

so if give cemp_id 3 should manager id 109.

i have tried following query:

select c.customerid       childid,         p.customerid       parentid,         p.parentcustomerid grantparentid,         c.customername    customer_profile c         join customer_profile p           on c.parentcustomerid = p.customerid  

but works on 2 levels of hierarchy , need n levels of hierarchy.

if work sql server 2008 or higher:

declare @id int = 1  ;with cte (     select parentcustomerid, 0 level      customer_profile customerid = @id      union      select t.parentcustomerid, cte.level  + 1 level     customer_profile t     inner join cte on t.customerid = cte.parentcustomerid   )  select top 1 parentcustomerid cte order level desc