'Return a specific column of the deepest ancestor for all child records

I am researching recursion in SQL Server. I have the following query and I can't seem to get it to do what I want.

;WITH cte_txn AS (
SELECT
    vth_id,
    vth_pol_id,
    vth_moved_from_vth_id
FROM Variant_Transaction_Header
UNION ALL
SELECT
    e.vth_id,
    e.vth_pol_id,
    e.vth_moved_from_vth_id
FROM Variant_Transaction_Header e
JOIN cte_txn ON cte_txn.vth_moved_from_vth_id = e.vth_id
)

SELECT * FROM cte_txn;

If I specify a vth_id in the anchor (In this case "WHERE vth_id = 72418"), I get the following:

vth_id  vth_pol_id  vth_moved_from_vth_id
72418   NULL        57019
57019   NULL        53518
53518   803         NULL

Which is great. But now I want to convert the data above into:

vth_id  vth_pol_id  vth_moved_from_vth_id
72418   803         57019
57019   803         53518
53518   803         NULL

In other words, I want the vth_pol_id of the DEEPEST ancestor, regardless of which vth_id I plug in. What's the best way to do this?

EDIT: I should specify that I would want to get a result set that contains all branches, with the vth_pol_id essentially denoting which branch each record is on.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source