Oracle sorting connect by prior hierarchically
I have a table where there is a parent/child relationship defined, but there is no way to sort via hierarchical level.
For example consider this:
empid managerid name
js hd George
hd ab Mary
hs js Tom
ab xx John
Since i cannot use ORDER SIBLINGS BY for sorting in hierarchical way, how will i be able to sort this in hierarchical way like below?
empid managerid name
ab xx John
hd ab Mary
js hd George
hs js Tom
It would have been helpful to show us the queries you have attempted so far. It sounds like there is a misunderstanding somewhere.
For your very small sample data Eng. Samer T Eng. Samer T 's answer is correct. You don't need an order by clause. Oracle guarantees that rows will be returned in a hierarchical order in a connect by query. Oracle documentation on Hierarchical Queries:
What is not guaranteed is how rows within a same hierarchy level (siblings) will be sorted. But because your sample data only has a single child for every level, this effectively doesn't matter.
However, let's consider an example with a bit more data so that we have siblings.
create table Employee (
empid varchar2(50) not null,
managerid varchar2(50) not null,
name varchar2(50) not null
)
/
insert into Employee (empid, managerid, name) values ('js', 'hd', 'George');
insert into Employee (empid, managerid, name) values ('hd', 'ab', 'Mary');
insert into Employee (empid, managerid, name) values ('hs', 'js', 'Tom');
insert into Employee (empid, managerid, name) values ('ww', 'js', 'Waldo');
insert into Employee (empid, managerid, name) values ('ab', 'xx', 'John');
insert into Employee (empid, managerid, name) values ('ab2', 'xx', 'Other manager');
insert into Employee (empid, managerid, name) values ('st', 'ab2', 'Stan');
insert into Employee (empid, managerid, name) values ('lee', 'ab2', 'Lee');
alter table employee
add constraint employee_pk
primary key (empid)
/
If I then run the following connect by query without an order by clause:
select t.*, level
from employee t
start with t.managerid = 'xx'
connect by prior t.empid = t.managerid
I get (SqlFiddle):
empid managerid name level
----- --------- ---- -----
ab xx John 1
hd ab Mary 2
js hd George 3
hs js Tom 4
ww js Waldo 4
ab2 xx Other manager 1
lee ab2 Lee 2
st ab2 Stan 2
Notice how the hierarchy is respected, this is guaranteed. However, for siblings, the order is not guaranteed. For instance, there is no guarantee that John will always be listed before Other manager , that Tom will be listed before Waldo , or that Lee will be listed before Stan .
If you want to guarantee ordering within siblings, but without breaking the order of the hierarchy, that's precisely what the order siblings by clause is for.
So I don't know why you said:
i cannot use ORDER SIBLINGS BY for sorting in hierarchical way
Because, the Oracle documentation on Hierarchical Queries pretty much states the opposite:
In a hierarchical query, do not specify either ORDER BY or GROUP BY , as they will override the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.
Notice how it specifically recommends the use of order siblings by to sort siblings without breaking the hierarchical order of the results.
So let's say I want my results ordered hierarchically, but having siblings ordered alphabetically by descending name. Then I could adjust my earlier query to:
select t.*, level
from employee t
start with t.managerid = 'xx'
connect by prior t.empid = t.managerid
order siblings by name desc
And my results become (SqlFiddle):
empid managerid name level
----- --------- ---- -----
ab2 xx Other manager 1
st ab2 Stan 2
lee ab2 Lee 2
ab xx John 1
hd ab Mary 2
js hd George 3
ww js Waldo 4
hs js Tom 4
Notice how the hierarchy is still respected. But siblings John and Other manager are reordered so that Other manager 's entire tree of results is listed before John 's. Also, Waldo will now always appear before Tom , and Stan will always appear before Lee .
default "connect by" clause will return the result.
just start with head, I assume that 'xx' means no manager.
select t.*, t.rowid from emp_table t
start with managerid = 'xx'
connect by prior empid = managerid
Thanks all.... ORDER BY level gave the result i wanted. I wasn't knowing level is already defined in Oracle.
链接地址: http://www.djcxy.com/p/93920.html下一篇: Oracle排序按层次关联