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:
I need to write 2 queries. The red lines of the diagram above show what nodes I need to retrieve;
- Last left leg id.
- 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?
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.