Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find last record in a single-table chain (SQL Server)

Got this table in SQL Server 2005, which is used to maintain a history of merging 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 instance:

  • ID 1 is merged to ID 2
  • Later on, ID 2 is merged to ID 3
  • Again later, ID 3 is merged to ID 4

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

like image 614
tjeuten Avatar asked Jan 21 '23 10:01

tjeuten


1 Answers

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           
like image 71
Lieven Keersmaekers Avatar answered Feb 01 '23 08:02

Lieven Keersmaekers