How to get super parent from table with self relationship i need to create Function that will return the super parent of a given id from a table 

the table look like table1 (id, parentId, someData)
the parentId is related to the id

for example i have 1 - 2  - 3  - 4 id of each row


id   parentId
1    null
2    1
3    2
4    3


now when i give the Function 4 as parameter it will return 1

i'm using SQL server 2008

how can this be done
Something like this

/*
create table #table1 (id int, parentId int)

INSERT INTO #table1
VALUES (1,null),
(2,1)
,(3,2)
,(4,3)
,(5,4)
,(10, null)
,(11,10)
,(8,11)
*/
declare @child int; 
set @child=4;
with prt as
(select 1 rn,id, parentid from #table1 where id=@child
union all
select rn+1 rn, t.id,t.parentid from #table1 t join prt on prt.parentid=t.id
where t.parentid is not null
)
select top 1 parentid from prt order by rn desc


Recursive CTE