Binary Search Query using SQL

Question

I have a binary tree presented in a SQL Server 2014 table:

UserID ParentUserID Position 
----------------------------
1      Null         Null     <-- ROOT
2      1            Left
3      1            Right    <-- Last Right for ID=1 (CTE query return 3)
4      2            Left
5      4            Left
6      5            Left
7      6            Left     <-- Last Left for ID=1 (CTE query return 6)

To get last left id and last right id I am using CTE query:

; with  left_hand_recurse as
(
        select  UserID
        ,       ParentUserID
        ,       1 as depth
        from    Table1 where ParentUserID is null
        union all
        select  child.UserID
        ,       child.ParentUserID
        ,       parent.depth + 1
        from    left_hand_recurse parent
        join    Table1 child
        on      parent.UserID = child.ParentUserID
                and position = 'Left'
)
select  top 1 *
from    left_hand_recurse
order by
        depth desc
;

and

; with  right_hand_recurse as
(
        select  UserID
        ,       ParentUserID
        ,       1 as depth
        from    Table1 where ParentUserID is null
        union all
        select  child.UserID
        ,       child.ParentUserID
        ,       parent.depth + 1
        from    right_hand_recurse  parent
        join    Table1 child
        on      parent.UserID = child.ParentUserID
                and position = 'Right'
)
select  top 1 *
from    right_hand_recurse 
order by
        depth desc
;

It is working fine. This way I can get the last UserID in left or right side for root (for ParentUserID == 1)

I need to modify the CTE query to get same result but for specific ParentUserID which I want pass as a parameter @ParentUserID.

How to achieve this goal?

Answer 1

Just change this line in each CTE:

    from    Table1 where ParentUserID is null

to:

    from    Table1 where ParentUserID = @ParentId