Selecting leaf id + root name from a table in oracle

Question

I have a table that is self referencing, with id, parentid (referencing id), name, ordering as columns.

What I want to do is to select the first leaf node of each root and have a pairing of the id of the leaf node with the name of the root node.

The data can have unbounded levels, and siblings have an order (assigned by the "ordering" column). "First leaf node" means the first child's first child's first child's (etc..) child.

The data looks something like this, siblings ordered by ordering:

A
--a
--b
----b.1
----b.2
----b.3
B
--c
----c.1
----c.2
--d
C
--e
----e.1
------e.1.1

I want to be able to produce a mapping as follows:
name of A, id of a
name of B, id of c.1
name of C, id of e.1.1

This is the sql I'm using to achieve this, but I'm not too sure if it will recurse correctly for unbounded levels:

select id,  
       connect_by_root name name 
  from table 
 where connect_by_isleaf = 1 
   and ((level = 2 and ordering = 1) 
    or (level > 2 and ordering = 1 and prior ordering = 1)) 
start with parentid is null 
connect by prior id = parentid;

Is there any way I can make rewrite the sql to make it unbounded?

Answer 1

Hi Filbert,

I would use a subquery:

SQL> SELECT root_name, MIN(leaf_name) first_leaf
  2    FROM (SELECT id, connect_by_root(r.NAME) root_name, r.NAME leaf_name
  3             FROM recurse r
  4            WHERE connect_by_isleaf = 1
  5            START WITH parentid IS NULL
  6           CONNECT BY PRIOR id = parentid)
  7   GROUP BY root_name;

ROOT_NAME  FIRST_LEAF
---------- ----------
A          a
B          c.1
C          e.1.1

This will give you the first leaf (ordered by the leaf name) for each root.

---

Update

This is the script I used to generate your data:

CREATE TABLE recurse (
   ID NUMBER PRIMARY KEY, 
   name VARCHAR2(10), 
   parentid NUMBER REFERENCES recurse (ID));

INSERT INTO recurse VALUES (1, 'A', '');
INSERT INTO recurse VALUES (3, 'b', 1);
INSERT INTO recurse VALUES (4, 'b.1', 3);
INSERT INTO recurse VALUES (5, 'b.2', 3);
INSERT INTO recurse VALUES (6, 'b.3', 3);
INSERT INTO recurse VALUES (7, 'B', '');
INSERT INTO recurse VALUES (8, 'c', 7);
INSERT INTO recurse VALUES (9, 'c.1', 8);
INSERT INTO recurse VALUES (10, 'c.2', 8);
INSERT INTO recurse VALUES (11, 'd', 7);
INSERT INTO recurse VALUES (12, 'C', '');
INSERT INTO recurse VALUES (13, 'e', 12);
INSERT INTO recurse VALUES (14, 'e.2', 13);
INSERT INTO recurse VALUES (15, 'e.1', 13);
INSERT INTO recurse VALUES (16, 'a', 1);
INSERT INTO recurse VALUES (20, 'e.1.1', 15);

As you can see I anticipated that your ordering would not be by name (this is really unclear from your question though).

Now suppose you want to order by ID (or really any other column it doesn't matter), you want to use analytics, for example:

SQL> SELECT DISTINCT root_name,
  2         first_value(leaf_name)
  3            over(PARTITION BY root_name ORDER BY ID) AS first_leaf_name
  4    FROM (SELECT id, connect_by_root(r.NAME) root_name, r.NAME leaf_name
  5             FROM recurse r
  6            WHERE connect_by_isleaf = 1
  7            START WITH parentid IS NULL
  8           CONNECT BY PRIOR id = parentid)
  9   ORDER BY root_name;

ROOT_NAME  FIRST_LEAF_NAME
---------- ---------------
A          b.1
B          c.1
C          e.2