Re: Tree walking ordering at top of tree

On Jun 28, 5:09 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> 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

Hi

Thanks for the responses. I'd better describe what I'm trying to achieve, and what actually happens, using Robert's select:

> select e.id,e.manager_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;

Gives

ID	MANAGER_ID	LPAD(E.NAME,LENGTH(E.NAME)+(LEVEL-1)*2,'-')
596		Smith
7		Ince
604		Clark
606	604	--Dibble
610	606	----Cuthbert
612	606	----Grub
614	612	------Smith
608	604	--Davidson
85		Brown
234		Jones
568		Williams
584		Gibson
84	584	--Blake
600	84	----Donaldson

I'm aiming to get the top-level names in alphabetical order, but keep the hierarchies intact:

ID	MANAGER_ID	LPAD(E.NAME,LENGTH(E.NAME)+(LEVEL-1)*2,'-')
85		Brown
604		Clark
606	604	--Dibble
610	606	----Cuthbert
612	606	----Grub
614	612	------Smith
608	604	--Davidson
584		Gibson
84	584	--Blake
600	84	----Donaldson
7		Ince
234		Jones
596		Smith
568		Williams

Regards

David Received on Mon Jun 30 2008 - 04:32:02 CDT