CODEWITHSUNDEEP
sqloracle-database
Jabda
Jabda

Reputation: 1792

Oracle Connect BY return top level only

I am trying to get the top most parent of a record using connect by

Select distinct parent_box_id,box_id, 
LEVEL,
SYS_CONNECT_BY_PATH(box_id, '>') "lineage"

  FROM box_lineage lineage 
  where lineage.position = 1 
        START WITH   box_id='00112233   '
   CONNECT BY   box_id = PRIOR parent_box_id
      ORDER SIBLINGS BY box_id;

result

    parent child level hierarchy
    123456  789456  3   >00112233>963258>789456
    789456  963258  2   >00112233>963258
    963258  00112233    1   >00112233

What I want is only 123456 789456 3 >00112233>963258>789456

I don't know what the top level is it could be 2,3,4 5, 6

I tried and level >=3 and level 1 but I only get the bottom level

I've read the other answers on here but none worked

Upvotes: 2

Views: 8940

Answers (4)

shevisi tamid
shevisi tamid

Reputation: 101

If I understood the question correctly, here is what I did:

Using the common employees example this will do it:

SELECT *
FROM
  ( SELECT DISTINCT lowest_employee_id as employee_id,
    manager_id,
    l AS path_level,
    path,
    rank() over (partition BY lowest_employee_id order by l DESC) rnk
  FROM
    (
SELECT employee_id ,
      manager_id,
      level l,
      -- the following line is just for visualization purpose
      SYS_CONNECT_BY_PATH(manager_id, ' > ') path,
      connect_by_root(employee_id ) lowest_employee_id
    from employees
    start with employee_id = 116
    connect by prior manager_id = employee_id
    )
  )
WHERE rnk = 1

Explanation:

connect_by_root will store the starting root for every level, then we rank the levels by its level number and then we filter for only the latest

Upvotes: 1

user5683823
user5683823

Reputation:

Just to illustrate John Ashley's correct answer, here is a similar query against the EMPLOYEES table in the HR schema (which exists in pretty much all Oracle installations).

Note - all the credit should go to John Ashley, here I am just illustrating his solution.

select employee_id, manager_id, level, sys_connect_by_path(employee_id, '?') as path
from employees
where connect_by_isleaf = 1
start with employee_id = 116
connect by prior manager_id = employee_id
;

EMPLOYEE_ID MANAGER_ID      LEVEL PATH
----------- ---------- ---------- ---------------
        100                     3 ?116?114?100

(Note: in the EMPLOYEES table in HR, the "top" is NULL, the MANAGER_ID for the CEO of the company - this is why the "parent" column shows NULL.)

Upvotes: 3

Unoembre
Unoembre

Reputation: 555

Add AND CONNECT_BY_ISLEAF = 1 to the where clause.

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133370

try using level 

Select distinct parent_box_id,box_id, 
LEVEL,
SYS_CONNECT_BY_PATH(box_id, '>') "lineage"

  FROM box_lineage lineage 
  where lineage.position = 1 
  and level = 3
  CONNECT BY   box_id = PRIOR parent_box_id
  START WITH   box_id ='00112233   '
  ORDER SIBLINGS BY box_id;

..for max try this 

  Select distinct parent_box_id,box_id, 
  LEVEL,
  SYS_CONNECT_BY_PATH(box_id, '>') "lineage"

  FROM box_lineage lineage 
  where lineage.position = 1 
  and level =  (   

      Select max( LEVEL) 
      FROM box_lineage lineage 
      where lineage.position = 1
      CONNECT BY   box_id = PRIOR parent_box_id
      START WITH   box_id ='00112233   '

  )
  CONNECT BY   box_id = PRIOR parent_box_id
  START WITH   box_id ='00112233   '
  ORDER SIBLINGS BY box_id;

Upvotes: 1

Related Questions