CODEWITHSUNDEEP
oracle-databaseparentchildren
user2783058
user2783058

Reputation: 43

Oracle query to list all parents before children

I have a table which has child#, parent# like the following : 

child# |  parent#
------------------
10     | NULL
20     | NULL
2      | 1
1      | 10 
50     | 10
6      | 5 
5      | 2

There is no ordering of numbers, i.e. 1 can be parent of 10 and 10 can be parent of 20.

I want an ORACLE SQL query which lists all parents first, followed by their children.

I want a temporary table like following:

child# | parent#
----------------
10     | NULL
20     | NULL
1      | 10
2      | 1 
50     | 10
5      | 2

I want to traverse this temporary table and process each rows, so for that I need to make sure parent is listed before the children rows.

Upvotes: 2

Views: 1115

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53525

select level,child,parent
from your_table
start with t2.parent is null
connect by prior t2.child = t2.parent
order by level

OUTPUT:

LEVEL   CHILD   PARENT
1       10      (null)
1       20      (null)
2       1       10
2       50      10
3       2       1
4       5       2
5       6       5

Link to fiddle

Upvotes: 1

Related Questions