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

Question

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.

Tim Biegeleisen · Accepted Answer · 2018-03-09T11:49:31.970

1

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

edited Mar 09 '18 at 11:49

answered Mar 09 '18 at 08:45

Tim Biegeleisen

502,043
27
286
360

I am not able to understand why we need row_number(). Isn't it easily achievable only using Max() and Group By? – Ankit Bajpai Mar 09 '18 at 08:46
@AnkitBajpai Yes, that might work. But row number is much more flexible should requirements change in the future. – Tim Biegeleisen Mar 09 '18 at 08:46
Basically correct answer, but there is a mistake: you should partition by "connect_by_root id" instead of "id". Please check also my answer! – riskop Mar 09 '18 at 11:46

score 1 · Answer 2 · edited Mar 09 '18 at 09:00

1

Just use group by :

    create table t23 ( dt varchar(10) ,id1 int );
    insert all
      into t23 values('A',1)
      into t23 values('A',2)
      into t23 values('A',3)
      into t23 values('B',1)
      into t23 values('B',2)
      into t23 values('B',3)
      into t23 values('B',4)  
    select * from dual;
    select * from t23;
    select dt , max(id1)id1 from t23 group by dt;

edited Mar 09 '18 at 09:00

Barbaros Özhan

59,113
10
31
55

answered Mar 09 '18 at 08:48

vivek kumar

63
3

score 1 · Answer 3 · answered Mar 09 '18 at 09:00

The connect_by_isleaf function might be what you're after, e.g.:

WITH dbset AS (SELECT 'A' ID, 1 CHILD, NULL PARENT FROM dual UNION ALL
               SELECT 'A' ID, 2 CHILD, 1 PARENT FROM dual UNION ALL
               SELECT 'A' ID, 3 CHILD, 2 PARENT FROM dual UNION ALL
               SELECT 'B' ID, 1 CHILD, NULL PARENT FROM dual UNION ALL
               SELECT 'B' ID, 2 CHILD, 1 PARENT FROM dual UNION ALL
               SELECT 'B' ID, 3 CHILD, 2 PARENT FROM dual UNION ALL
               SELECT 'B' ID, 4 CHILD, 3 PARENT FROM dual)
SELECT connect_by_root ID AS root,
       LEVEL lvl,
       CONNECT_by_isleaf,
       CHILD, PARENT
FROM   dbset
WHERE  CONNECT_by_isleaf = 1
START WITH ID IN ('A', 'B') AND PARENT IS NULL
CONNECT BY NOCYCLE PRIOR CHILD = PARENT
           AND ID = PRIOR ID
ORDER BY root;

ROOT        LVL CONNECT_BY_ISLEAF      CHILD     PARENT
---- ---------- ----------------- ---------- ----------
A             3                 1          3          2
B             4                 1          4          3

score 1 · Answer 4 · answered Mar 09 '18 at 11:44

This is basically the same as Tim Biegeleisen's answer, just completed with test data, result, sql fiddle and a correction about the partitioning condition.

Note: I am happily delete this answer as soon as Tim's answer is corrected and completed.

Test data:

create table dbset (id varchar(1), child varchar(1), parent varchar(1) );
insert into dbset values('A','A','A');
insert into dbset values('X','A','X');
insert into dbset values('Z','X','Z');
insert into dbset values('B','6','6');
insert into dbset values('G','6','7');
insert into dbset values('H','7','8');
insert into dbset values('I','8','9');

Query:

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;

Result:

| ROOT | LVL |
|------|-----|
|    A |   3 |
|    B |   4 |

Sql fiddle: http://sqlfiddle.com/#!4/37c70/8

Yogesh Sharma · Answer 5 · 2018-03-09T08:49:55.280

0

Use ranking function row_number() to get the top most record for each root.

select * from 
(
    select *,
         row_number() over (partition by id order by level desc) Seq 
    from table
) t
where Seq = 1

edited Mar 09 '18 at 08:49

answered Mar 09 '18 at 08:43

Yogesh Sharma

49,870
5
26
52

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

5 Answers5