Got this table in SQL Server 2005, which is used to maintain a history of merging operations:
Now I need a query that takes the original FROM_ID as input, and returns the last available TO_ID.
So for instance:
So the query I'm trying to put together will take as input (in the WHERE clause I presume) ID 1, and should give me the last available TO_ID as a result, in this case 4.
I suppose I need some recursion logic, but don't really know how to start.
Thanks !
Mathieu
Using a CTE would work.
Testscript
DECLARE @IDs TABLE (
FromID INTEGER
, ToID INTEGER
)
INSERT INTO @IDs
SELECT 1, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 4
SQL Statement
;WITH q AS (
SELECT FromID, ToID
FROM @IDs
UNION ALL
SELECT q.FromID, u.ToID
FROM q
INNER JOIN @IDs u ON u.FromID = q.ToID
)
SELECT FromID, MAX(ToID)
FROM q
WHERE FromID = 1
GROUP BY
FromID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With