Re: Tree walking ordering at top of tree

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

.

Relevant Pages

  • Re: Books on why programmers think more than coding
    ... > The reason the technicians all left, was becuase the boss was a total PHB ... This "hierarchy" can be inefficient too, ... employees badly and such... ... all...that in the "Chinese Whispers" chain up the hierarchy, a "manager" ...
    (alt.lang.asm)
  • Re: Figuring out relationships between tables.
    ... I've got a database where I need to hold information on employees. ... An employee can only have 1 manager/supervisor but the hierarchy can be varying depths. ... I don't want a Manager table that duplicates all the information for employees for the managers. ...
    (microsoft.public.sqlserver.server)
  • Re: Tree walking ordering at top of tree
    ... create table employees ( ...   manager_id number, ... connect by prior e.id = e.manager_id; ... ordered as the hierarchy is structured. ...
    (comp.databases.oracle.server)
  • Re: Hierarchy without tree
    ... > roles...whatever) without using tree view on the web using asp.net. ... how would you have user add/remove people from that hierarchy? ... to show a single manager and the list of their employees as a diagram. ... control has already been created and that you can buy it for a small sum. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Hierarchical Subquery must omit nodes
    ... Excluded node can be in any depth of the hierarchy (i.e. it could be ... I've tried filtering the START WITH and works great but doesn't help ... FROM employees ...
    (comp.databases.oracle.misc)