Consult about looking for a report to the superior logic, if no one supervisor for on-the-job, will-CodePudding

Refer to
Name of the table - JOB
Emplid reportsto position
A 100, 101,
B, 101, 102
C 102 103

The SELECT A.E MPLID, A.R EPORTS_TO, B.E MPLID
The FROM PS_JOB A, PS_JOB B
WHERE p. OSIION_NBR=A.R EPORTS_TO

The actual result
B 101 a
C 102 b

Want the results
A 100
B 101 a
C 102 b

CodePudding user response:

 with tab1 as (
Select the 'a' a, 100 b, 101 c from dual union all 
Select the 'b' a, 101 b, 102 c from dual union all 
Select the 'c' a, 102 b, 103 c from dual 
) 
. Select a t1, a t1. B, t2. The from a tab1 t1, tab1 t2 
Where a t1. B=t2. C (+) 
The order by t1. B 
; 

With tab1 as (
Select the 'a' a, 100 b, 101 c from dual union all 
Select the 'b' a, 101 b, 102 c from dual union all 
Select the 'c' a, 102 b, 103 c from dual 
) 
. Select distinct t1, a t1. B, first_value (the prior t1. A) over (partition by t1. The order by a level desc) cc 
The from tab1 t1 
Connect by the prior t1. C=t1. B 
And level & lt;=2 
The order by t1. B 
;