'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 |
|---|
