A difficult SQL statement (Oracle 9i implements 10 Gb connect_by_isleaf, connect_by_root, and other functions)

There is an emp table, two fields, employee id and supervisor id. 1, emp_id, 2, manager_id
If you have the following information, an employee can correspond to one or more supervisor IDs, that is, an employee may have several supervisors.
Emp_id manager_id
001 101
001 102
101 201
102 202
002 102
003 103
103 203
201 301
203 303

Now you need to use any employee id to find the id of his supervisor. There may be more than one result.

If it is 001, the result is as follows:
Emp_id manager_id
001 301
001 202
........................
If it is 002, the result is as follows:
Emp_id manager_id
002 102
........................
If it is 103, the result is as follows:
Emp_id manager_id
103 303
......................................
The following table creation scripts and materials:
 

Create table emp
(
Emp_id VARCHAR2 (10 ),
Manager_id VARCHAR2 (10)
);

Insert into emp
(Emp_id, manager_id
)
VALUES ('001', '20180101'
);
Insert into emp
(Emp_id, manager_id
)
VALUES ('001', '20180101'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('20160301', '20160301'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('20160301', '20160301'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('002', '123'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('003 ', '123'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('20160301', '20160301'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('20160301', '20160301'
);
Insert into EMP
(Emp_id, manager_id
)
Values ('20160301', '20160301'
);
Commit;

Someone gave the following answer:

Select emp_id, manager_id
FROM (SELECT CONNECT_BY_ROOT (emp_id) emp_id, manager_id,
CONNECT_BY_ISLEAF v_isleaf
FROM emp
Connect by emp_id = PRIOR manager_id)
WHERE v_isleaf = 1;

This statement is very concise and uses the enhanced 10g connect by feature, such as determining whether the pseudo column connect_by_isleaf of the leaf node is used, and only the unary operator connect_by_root that returns the result from the root row is used, which is good. But the questioner said that 9i is used, which is a little troublesome. Can I use an SQL statement instead of PLSQL? After in-depth research, I provided My SQL:

SELECT emp_id, manager_id
FROM (SELECT FIRST_VALUE (emp_id) OVER (partition by part order by lev) emp_id,
ROW_NUMBER () OVER (partition by part order by levdesc) rn,
Part, manager_id AS manager_id
FROM (SELECT emp_id, manager_id, LEVEL lev, (ROWNUM-LEVEL) part
FROM emp
Connect by emp_id = PRIOR manager_id ))
WHERE rn = 1;

9i does not provide connect_by_isleaf and connect_by_root, but you can use the analysis function to implement its basic functions. The following is an analysis.

The innermost query:
Select emp_id, manager_id, level lev, (rownum-level) Part
From EMP
Connect by emp_id = prior manager_id;

Here we use reverse traversal from the leaf to the heel, and use the value of the (rownum-level) Part column to represent a path from the leaf to the root, to prepare for the partition conditions using the analysis function.

Layer 2 nested Query
Select first_value (emp_id) over (partition by Part order by eV) emp_id,
Row_number () over (partition by Part order by levdesc) Rn, part,
Manager_id as manager_id
From (select emp_id, manager_id, level lev, (rownum-level) Part
FROM emp
Connect by emp_id = PRIOR manager_id );

Partition by part, sort by column, and use the FIRST_VALUE operation to display the leaf nodes in the first column;
Partition by part, sort by column, and then use the ROW_NUMBER () function to divide the level. The first level is the root node.

Nested query at the outermost layer to obtain the required result output.