Re: Tree walking ordering at top of tree

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 28 Jun 2008 01:45:05 +0200
Message-ID: <48657B81.3040005@gmail.com>


David schrieb:
> Hi
>
> I have the following query against the table
> create table employees (
> id number,
> manager_id number,
> name varchar2(100));
>
> select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-')
> from employees e
> start with e.id in (select e2.id
> from employees e2
> where e2.manager_id is null
> connect by prior e2.manager_id = e2.id)
> connect by prior e.id = e.manager_id;
>
> This returns a list of employees as a hierarchy, with the managers at
> the top and employees below, ordered as the hierarchy is structured.
>
> However, not all employees have a manager, and there are several top
> level managers. I want to list the level 1 employees in alphabetical
> order, without affecting the display of the hierarchy levels where
> they exist beneath these.
>
> Is this possible?
>
> Many thanks
>
> David

It is not quite clear, what are your requirements, maybe, it will be easier to provide a solution, if you could post a sample with test data and required output. According to your query, all employees without manager will be automatically placed on top of your hierarchy (i.e. they are manager regardless they have employees or not). I can't understand as well the purpose of the subquery - in my opinion, more readable is the equivalent:

select e.id,lpad(e.name,length(e.name)+(level-1)*2,'-') from employees e
start with e.manager_id is null
connect by prior e.id = e.manager_id;

Best regards

Maxim Received on Fri Jun 27 2008 - 18:45:05 CDT

Original text of this message