SQL Query to get Last binary left or right position

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

UserID ParentUserID Position 
------------------------------
1      null         null     <-- ROOT
2      1            Left
3      1            Right    <-- Last right for ID=1 (query should return 3)
4      2            Left
5      4            Left
6      2            Left
7      6            Left     <-- Last left for ID=1 (query should return 6)

Here is graphic representation:

  • SSIS - Derived Column Calculation Error
  • Get along with image sql server linked servers from PostgreSQL
  • Dynamic Comma Seperated string into different column
  • Order by in Inner Join
  • Business Intelligence for Visual Studio 2013 extraction error
  • Dapper Semi-Dynamic Typing?
  • enter image description here

    I need to write 2 queries. The red lines of the diagram above show what nodes I need to retrieve;

    1. Last left leg id.
    2. Last right leg id

    How can I achieve my goal… should I use recursive query?

    Could you please provide an example of queries I need?

  • Convert Number to Date in SQL Server with Exception in Month-End
  • How to create multiple one to one's
  • Issues with SQL Server MERGE statement
  • How to make a remote connection with SQL Server 2008 with ADO.NET entity Data model from C# winform
  • SQL Server query issue - ambiguous column
  • Remove trailing empty space in a field content
  • One Solution collect form web for “SQL Query to get Last binary left or right position”

    You can recurse down the left or right hand side with a recursive common-table expression (CTE.) Using select top 1 * ... order by depth you can find the deepest node:

    ; 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
    ;
    

    Example at SQL Fiddle.

    P.S. Your example data makes both 6 and 4 a left parent of 2. I assume that 6 is the left parent of 5.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.