Search code examples
sqloraclehierarchical-query

How to select highest level from 2 different trees in same command

I have a dataset that has the following appearence. 

A 1
A 2
A 3
B 1
B 2
B 3
B 4

Which is a result from using the following command

select 
    connect_by_root id as root,
    level lvl

from
    dbset
start with id in ('A','B')
connect by nocycle child = prior parent)

I want the result

A 3
B 4

That is, I want to extract the topmost root in each of the trees. Ideally I would like this to be done within the same command I have, but I am too much of a novice within the area to know how to.

Solution

  • Use ROW_NUMBER:

    SELECT root, lvl
FROM
(
    SELECT 
        connect_by_root id AS root,
        level lvl,
        row_number() over (PARTITION BY connect_by_root id ORDER BY level DESC) rn
    FROM dbset
    START WITH id IN ('A','B')
    CONNECT BY NOCYCLE child = prior parent
) t
WHERE rn = 1