How do I get top element of a certain type?

Question

Let's say I have something similar to this (hierarchy of folders on drive C:).

I want to get top folder of a given folder (in this case I chose '1'), not the disc itself, how do I do this?

Hierarchy can have various levels.

with data as 
(
select '1' name, 'folder' type, 'docs' parent from dual union
select '2' name, 'folder' type, 'docs' parent from dual union
select '3' name, 'folder' type, 'docs' parent from dual union
select 'docs' name, 'folder' type, 'MyFolder' parent from dual union
select 'MyFolder' name, 'folder' type, 'C:\' parent from dual union
select 'C:\' name, 'Drive' type, null parent from dual 
)
select name, level from data
start with name = '1'
connect by prior parent = name
order by level;

expected output: 'Myfolder', as it doesn't have any other folder as parent.

Added it, thank you, user75ponic, I may be not clear enough about my goals — Bobby, Aug 27 '18 at 10:39

score 1 · Answer 1 · answered Aug 27 '18 at 10:42

Here's one option:

SQL> with data as
  2  (
  3  select '1' name       , 'folder' type, 'docs'     parent from dual union
  4  select '2' name       , 'folder' type, 'docs'     parent from dual union
  5  select '3' name       , 'folder' type, 'docs'     parent from dual union
  6  select 'docs' name    , 'folder' type, 'MyFolder' parent from dual union
  7  select 'MyFolder' name, 'folder' type, 'C:\'      parent from dual union
  8  select 'C:\' name     , 'Drive'  type, null       parent from dual
  9  ),
 10  inter as
 11    (select name, level lvl, type
 12     from data
 13     start with name = '1'
 14     connect by prior parent = name
 15    )
 16  select name
 17  from inter
 18  where lvl = (select max(lvl) from inter
 19               where type = 'folder');

NAME
--------
MyFolder

SQL>

Jacob · Answer 2 · 2018-08-27T11:04:51.673

1

Please try the below,

    WITH data
     AS (SELECT '1' name, 'folder' TYPE, 'docs' parent FROM DUAL
         UNION
         SELECT '2' name, 'folder' TYPE, 'docs' parent FROM DUAL
         UNION
         SELECT '3' name, 'folder' TYPE, 'docs' parent FROM DUAL
         UNION
         SELECT 'docs' name, 'folder' TYPE, 'MyFolder' parent FROM DUAL
         UNION
         SELECT 'MyFolder' name, 'folder' TYPE, 'C:\' parent FROM DUAL
         UNION
         SELECT 'C:\' name, 'Drive' TYPE, NULL parent FROM DUAL)
    SELECT name
      FROM data
     WHERE parent = (SELECT name
                       FROM data
                      WHERE TYPE = 'Drive')
START WITH name = '1'
CONNECT BY PRIOR parent = name

Output

   NAME    
--------
MyFolder

Test with the real data and let us know the outcome.

edited Aug 27 '18 at 11:04

answered Aug 27 '18 at 10:55

Jacob

14,463
65
207
320

It works, but only mentioning type of parent, which I'd like to avoid. But thanks, that's neat! – Bobby Aug 28 '18 at 10:39
@Bobby The top most folder's parent will be able always a drive or disc, correct me if I am wrong. Hence, I have included the condition to get the name by adding a sub-query rather than hard code the name. – Jacob Aug 28 '18 at 12:55

How do I get top element of a certain type?

2 Answers2