Posted by Jack Turnbull on 08/31/06 15:17

Thanks Ed,

Put me on the right track - ended up creating a function that returned a
table using your iteration.

Thanks,
Jack

"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:URAJg.6350$S_5.5724@tornado.socal.rr.com...
>
> Yes.
> </pedant>
>
> declare @ancestor_id int, @candidate_id int
> set @ancestor_id = @id
> set @candidate_id = @id
> while @candidate_id is not null
> begin
> set @candidate_id = (
> select ParentID
> from table
> where id = @ancestor_id
> )
> if @candidate_ancestor_id is not null
> begin
> set @ancestor_id = @candidate_id
> end
> end

[Back to original message]