Reputation: 35567
I have the following Oracle SQL query:
select id_emp_no,
LEVEL,
CONNECT_BY_ISLEAF isleaf
from my_table
start with id_emp_no = :x
connect by prior my_mgr = id_emp_no
ORDER BY level
This produces the following output:
ID_EMP_NO LEVEL ISLEAF
11 1 0
22 2 0
33 3 0
44 4 0
55 5 0 <<====
66 6 1
Based on the above output, the actual result ID_EMP_NO that I am specifically after is where ID_EMP_NO = 55, that is the record preceeding the max level and the record prior to ISLEAF equalling 1.
So I only want to return the record where ID_EMP_NO = 55.
Upvotes: 0
Views: 3199
Reputation: 10941
Keep in mind that prior
is nothing but an operator specifying a value of this column from an upper level of the hierarchy, which means you can use it (almost) anywhere in your query:
select prior id_emp_no
from my_table
where CONNECT_BY_ISLEAF = 1
start with id_emp_no = 11
connect by prior my_mgr = id_emp_no
| PRIORID_EMP_NO |
------------------
| 55 |
Upvotes: 4