The advice given below is correct, I'll try to expand...

The "start with" clause identifies the first row to start traversing from.
The criteria can be anything and can also be unrelated to the "connect by
prior" clause. You might use "user_superior_id IS NULL" to start at the
top of the hierarchy (common).

The "connect by prior" clause explains how to join the rows previously retrieved
to the next set of rows - if that makes sense. In the example below the
clause is saying "get rows which have their user_superior_id field set to
the value which I previously retrieved in the user_id column" - ie: traverse
DOWN the hierarchy.

Finally, if your first post you talked about efficiency. For a large table
recursive SQL can perform very badly. The trick is to index the columns
in the "connect by prior" clause. A concatenated index can be used (sorry,
can remember correct order off the top of my head) is you will only traverse
in one direction. Otherwise, index each side of the clause (ie: user_id
and user_superior_id) separately and traversing up or down will be efficient.
You should find that up to a few thousand rows no indexing will be required
- after that performance degrades rapidly without indexing.

If you have any more questions send me an email - Unfortunately I have spent
far too much time working with recursive SQL.

"Joe" <jo@globo.com> wrote:
>
>>select user_id, user_name
>>from emp

>
>START WITH user_id = NNN
>
>>connect by prior user_id = user_superior_id

>
>"ora" <a@b.com> wrote:
>>
>>Pl try
>>
>>select user_id, user_name
>>from emp
>>connect by prior user_id = user_superior_id
>>
>>Pl let me know if it worked
>>
>>
>>
>>"Mallik S" <msmallik@yahoo.com> wrote:
>>>
>>>Hi,
>>>
>>>Can anybody help me to write an efficient sql query
>>>to return employees at the leaf level with all his superiors in
>>>the hierarchy. All employees in the hierarchy are in the same table
>>>with his/her supervisor code.
>>>The table will have several thousands of employees.
>>>
>>>The table structure is something like this
>>>
>>>user_id
>>>user_name
>>>user_superior_id
>>>
>>>Thanks
>>>
>>>Mallik
>>>

>>

>