CODEWITHSUNDEEP
oracle-database
Naga K
Naga K

Reputation: 753

Get managers first from table and employees later

I have a table like this 

NAME -- MANAGER
10 -- 85
85 -- 45
45 -- 52
52 -- null

I want an output like this, where manager is put first in an hierarchy and then the employees

 NAME -- MANAGER

  52 -- null

  45 -- 52

  85 -- 45

  10 -- 85

Can anyone help me do it. I did inner join like below, but it didn't work 

select * from TABLEMAIN

 inner join TABLEMAIN t2 ON TABLEMAIN.MANAGER = t2.NAME

 order by TABLEMAIN.NAME

Please help me arrive at a solution

Upvotes: 0

Views: 51

Answers (1)

San
San

Reputation: 4538

Can be achieved using start with and connect by

WITH TABLEMAIN(NAME,MANAGER) AS
    (SELECT 10 ,85 FROM dual UNION ALL
    SELECT 85 , 45 FROM dual UNION ALL
    SELECT 45 ,52 FROM dual UNION ALL
    SELECT 52 ,null FROM dual )
----
-- End of data
----
SELECT * FROM TABLEMAIN
START WITH manager IS NULL
CONNECT BY PRIOR NAME = manager

Output:

NAME MANAGER
52  
45  52
85  45
10  85

Upvotes: 2

Related Questions