Recursively get last record from same table parent child

Question

I want to get last reference Id from same table. I have following table.

 ID   UserId   DelegatedToUserId
 1      100       101
 2      101       102
 3      102       103
 4      103       NULL
 5      104       109

I just can't get my head around. I know what I want, just simply can't get it out on the screen. So when I ask for 100 query should return 103, when I ask 101 or 102 again it should return 103. when user enters 104 it should return 109

And when I ask for 103 it should return 103 as there is no delegate for this.

can this be done in single sql query ?

*"can this be done in single sql query?"* Yes, the common way is to use a recursive Common Table Expression (rCTE). Have you used these before, or performed any research on them? — Thom A, Mar 26 '19 at 09:36
Also, a query would not return any rows if they input `104`, as there is no user `109` in your table. As user `104` has no parent (`109` doesn't exist), they are an orphan and so when you return the "top parent row" that won't exist. This is different to `103`, as that specifically states it has no parent; it can be identified as the "root". — Thom A, Mar 26 '19 at 09:43

PSK · Answer 1 · 2019-03-26T09:51:54.130

can this be done in single sql query ?

If you don't know till what level the hierarchy can go, you need to use recursive CTE. If it is just 1 or 2 level, you can do it without recursive CTE in a single query.

You can use recursive CTE like following to get the desired output.

;WITH mytest 
     AS (SELECT P.userid, 
                P.delegatedtouserid, 
                1 AS LVL 
         FROM   @table P 
         WHERE  userid = 100 
         UNION ALL 
         SELECT P1.userid, 
                P1.delegatedtouserid, 
                M.lvl + 1 AS LVL 
         FROM   @table P1 
                INNER JOIN mytest M 
                        ON M.delegatedtouserid = P1.userid) 

SELECT TOP 1 WITH ties * 
FROM   mytest 
ORDER  BY Row_number() OVER (ORDER BY lvl DESC);

Note: Replace @table with your actual table name.

Online Demo

score 2 · Accepted Answer · answered Mar 26 '19 at 09:51

Personally, I would go with this:

DECLARE @UserID int = 103;

WITH VTE AS
    (SELECT *
     FROM (VALUES (1, 100, 101),
                  (2, 101, 102),
                  (3, 102, 103),
                  (4, 103, NULL),
                  (5, 104, 109)) AS V (ID, UserID, DelegatedToUserID) ),
rCTE AS
    (SELECT V.ID,
            V.UserID,
            V.DelegatedToUserID
     FROM VTE V
     WHERE UserID = @UserID
     UNION ALL
     SELECT V.ID,
            V.UserID,
            V.DelegatedToUserID
     FROM rCTE r
          JOIN VTE V ON r.DelegatedToUserID = V.UserID)
SELECT *
FROM rCTE
WHERE rCTE.DelegatedToUserID IS NULL;

As I mentioned in the comments, however, passing 104 will return no rows as user 109 does not exist in the table.

Recursively get last record from same table parent child

2 Answers2

Linked