2

I have a tree data and I am trying to select only the root parents. The data may be subset of a larger set so it is possible that parent may not be empty. I would like to top most level for each tree in the data set.

 with test_data as (
 select '1' ID,'100' name, null parent  from dual
 union
 select '2' ID,'200' name, null parent  from dual
 union
 select '3' ID,'300' name, null parent  from dual
 union
 select '1.1' ID,'1.100' name, '1' parent  from dual
 union
 select '2.1' ID,'2.100' name, '2' parent  from dual
 union
 select '3.1' ID,'3.100' name, '3' parent  from dual
 union
 select '3.1.1' ID,'3.1.100' name, '3.1' parent  from dual
 union
 select '3.1.2' ID,'3.1.2.100' name, '3.1' parent  from dual
 union
 select '4.1' ID,'4.100' name, '4' parent  from dual
 union
 select '4.1.1' ID,'4.1.100' name, '4.1' parent  from dual
 union
 select '4.1.2' ID,'4.1.2.100' name, '4.1' parent  from dual )
 select * from test_data
 start with parent is null
 connect by parent=prior id

I would like to see results as

     ID    NAME      PAR
    ----- --------- ---
    1     100
    2     200
    3     300
    4.1   4.100      4

Rowid 4 is not selected as part of subset is a parent, but since 4.1 is a top most in this data set, i would like to return that row. So basically, i would like to see all top most level records for each hierarchy.

Thank You.

Sannu
  • 1,202
  • 4
  • 21
  • 32

1 Answers1

4

One method is using not exists:

select id, name, parent
from test_data td
where not exists (select 1
                  from test_data td2
                  where td.parent = td2.id
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786