We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Binary Tree Nodes
  5. Discussions

Binary Tree Nodes

Sort by

recency

|

2477 Discussions

|

  • ThiagoTVarella
     + 0 comments

    This solution may be more performant for very large tables as it doesn't require the creation of a whole new DISTINCT set

    SELECT
    N,
    CASE
        WHEN P IS NULL THEN 'Root'
        WHEN EXISTS (SELECT 1 FROM BST AS B2 WHERE B2.P = B1.N) THEN 'Inner'
        ELSE 'Leaf'
    END
FROM BST AS B1
ORDER BY N

  • chaitanyanair93
     + 0 comments

    MYSql Solution 

    SELECT CASE
WHEN P IS NULL THEN CONCAT(N, " Root")
WHEN N IN (SELECT DISTINCT P FROM BST) THEN CONCAT(N," Inner")
ELSE CONCAT(N," Leaf")
END
FROM BST
ORDER BY N ASC;

  • floreaoana22
     + 0 comments

    SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Inner' ELSE 'Leaf' END AS NodeType FROM BST ORDER BY N;

  • ssmith1975_soc
     + 0 comments

    An alternative solution

    Using CTE and OUTER APPLY...

    WITH Parents AS (
    SELECT DISTINCT P 
        FROM BST
)

SELECT b.N
    , CASE 
        WHEN b.P IS NULL THEN 'Root'
        WHEN oa.P IS NULL THEN 'Leaf'
        ELSE
        'Inner'       
    END   
    FROM BST b
    OUTER APPLY (
        SELECT b2.P
            FROM Parents b2 
            WHERE b.N = b2.P      
    ) oa
    ORDER BY b.N
;

go

    x

  • hzerryn
     + 0 comments
    SELECT n,
CASE
    WHEN p IS NULL THEN 'Root'
    WHEN n NOT IN (SELECT p FROM bst WHERE p IS NOT NULL) THEN 'Leaf'
    ELSE 'Inner'
END
FROM bst
ORDER BY n