Re: Tree walking ordering at top of tree

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 30 Jun 2008 05:06:19 -0700 (PDT)
Message-ID: <ee8e8cef-33d1-4ca6-b91a-fb9eba1df18b@59g2000hsb.googlegroups.com>


On 30 Jun., 11:32, David <dknight.w..._at_googlemail.com> wrote:
> 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

Would *order siblings by name* not suffice for your needs? Best regards

Maxim Received on Mon Jun 30 2008 - 07:06:19 CDT

Original text of this message