Find the last entry in a single table chain (SQL Server)

Question

Get this table in SQL Server 2005, which is used to maintain a history of merge operations:

• Column FROM_ID (int)
• Column TO_ID (int)

Now I need a query that takes the original FROM_ID as input and returns the last available TO_ID.

So for example:

• ID 1 is combined with ID 2
• Later, ID 2 is combined into ID 3
• Again later ID 3 combines into ID 4

So the query I'm trying to collect will take ID 1 as input (in the WHERE clause I assume), and should give me the last available TO_ID, in this case 4.

I guess I need recursive logic, but don't know how to get started.

Thank!

Mathieu

Answer 1

CTE .

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

;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           

Answer 2

, , .

;WITH q AS (
    SELECT  1 AS LEVEL, FromID, ToID
    FROM    @IDs
    WHERE FromID=1
    UNION ALL 
    SELECT  LEVEL + 1, q.FromID, u.ToID
    FROM    q
            INNER JOIN @IDs u ON u.FromID = q.ToID
)

SELECT TOP 1 ToID
 FROM q
ORDER BY LEVEL DESC