CODEWITHSUNDEEP
sqlsql-serversql-server-2012
armen
armen

Reputation: 1283

SQL : how to find leaf rows?

i have a self related table myTable like :

ID | RefID
----------
 1 | NULL
 2 | 1
 3 | 2
 4 | NULL
 5 | 2
 6 | 5
 7 | 5
 8 | NULL
 9 | 7

i need to get leaf rows on any depth

based on the table above, the result must be :

ID | RefID
----------
 3 | 2
 4 | NULL
 6 | 5
 8 | NULL
 9 | 7

thank you

PS: the depth may vary , here is very small example

Here is a visual demonstration of the sample data

Upvotes: 5

Views: 4118

Answers (4)

Ali Noureddine
Ali Noureddine

Reputation: 323

try this:

SELECT *
FROM
     my_table
WHERE
     id NOT IN
     (
      SELECT DISTINCT
         refId
      FROM
         my_table
      WHERE
         refId IS NOT NULL
      )

Upvotes: 0

paul
paul

Reputation: 22001

select  ID, RefId
from    myTable t1 left join myTable t2 on t1.ID = t2.RefID
where   t2.RefID is null

Upvotes: 3

xlecoustillier
xlecoustillier

Reputation: 16351

Try:

SELECT id,
       refid
FROM   mytable t
WHERE  NOT EXISTS (SELECT 1
                   FROM   mytable
                   WHERE  refid = t.id)

Upvotes: 8

gbn
gbn

Reputation: 432311

DECLARE @t TABLE (id int NOT NULL, RefID int NULL);

INSERT @t VALUES (1, NULL), (2, 1),  (3, 2),  (5, NULL), 
             (6, 5), (4, NULL), (7, 5), (8, NULL), (9, 8), (10, 7);

WITH CTE AS
(
    -- top level
    SELECT id, RefID, id AS RootId, 0 AS CTELevel FROM @t WHERE REfID IS NULL
    UNION ALL
    SELECT T.id, T.RefID, RootId, CTELevel + 1 FROM @t T JOIN CTE ON T.RefID = CTE.id
), Leafs AS
(
    SELECT
        id, RefID, DENSE_RANK() OVER (PARTITION BY CTE.RootId ORDER BY CTELevel DESC) AS Rn
    FROM CTE
)
SELECT
    id, RefID
FROM
    Leafs
WHERE
    rn = 1

Upvotes: 7

Related Questions