SQL & PL/SQL

It's easy enough to use a hierarchical query (via start with and connect by clauses) to display the complete hierarchy of a hierarchical table.

My question is, can a query be created to show the reverse ? In other words, can I start with an
arbitrary, low level row and show all the parents going up the hiearchy to the very top level ?

I've been able to show most of the parents for a lower level row but as soon as I try to include the top most row, I get the dreaded ORA-01436: CONNECT BY loop in user data error.

I'm familiar enought with the connect by loop error and can avoid it in most cases. But in this case, it seems like this kind of query just cannot be done since you can't prevent the query from trying to find the parent of the topmost parent.

Has anyone been able to do this ?


If an example helps, the hierarchical table I'm querying represents an organizational chart.

I can create a query that displays the complete organization like this:
CEO
VP
Director
Manager
Grunt

And I can create a query that displays all but the top most level for any sublevel. For example, if I query all the parents for "Grunt" I can get these results
Grunt
Manager
Director
VP

But I cannot get that query to also show the topmost, CEO level. At that point I get the connect by loop error.

Any ideas ?