Re: Tree walking ordering at top of tree

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 28 Jun 2008 18:09:09 +0200
Message-ID: <6cn615F3hdn3lU1@mid.individual.net>


On 28.06.2008 01:45, Maxim Demenko wrote:
> 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?

>
> 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;

I believe he wants to sort persons on the same hierarchy level by their names - something like this

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
order by level, name;

Kind regards

        robert Received on Sat Jun 28 2008 - 11:09:09 CDT

Original text of this message