Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Breadth First Search

770207Aug 22 2012 — edited Aug 22 2012
Hello,

I have a set of hierarchical data and I am trying to traverse through this using START WITH - CONNECT BY clause. This traversal is happening in Depth-First pattern. But I need the output in Breadth-First. I searched on this forum and found that using WITH clause, this can be achieved. I tried but still unclear on the approach. Can someone help me.

Input Table
FROM - TO
11 - 22
22 - 33
33 - 44
33 - 55
44 - 66
55 - 66
66 - 77
77 - 88

Query Used
SELECT to_element, max(rownum) order_position
FROM (SELECT from_element,to_element
FROM input_table
)tab
START WITH tab.from_element = 11
CONNECT BY PRIOR tab.to_element = tab.from_element
GROUP BY to_element
ORDER BY 2;

CURRENT OUTPUT
TO_ELEMENT - ORDER_POSITION
22 - 1
33 - 2
44 - 3
55 - 7
66 - 8
77 - 9
88 - 10


EXPECTED OUTPUT WITH BREADTH SEARCH
TO_ELEMENT - ORDER_POSITION
22 - 1
33 - 2
44 - 3
55 - 4
66 - 5
77 - 6
88 - 7

Any help in this regards will be really helpful.

Regards,
Nitin.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 19 2012
Added on Aug 22 2012
4 comments
1,201 views