3

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

Alaa Jabre
  • 151
  • 1
  • 5

1 Answers1

1

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

msi77
  • 1,155
  • 1
  • 7
  • 9