0

We have list of resources with their managers (not the complete hierarchy). We now need to retrieve the resource list such that resource's manager row should always appear before the resource row. I already tried hierarchical query, however given the data set, I'm not absolutely sure, if it's working correctly or not. Any pointers or clue, please to confirm the order?

Edit: Added last row in the data set and the query doesn't seem to be working anymore.

sqlfiddle

select level, emp, mgr 
from emp
where level = 1
connect by prior emp = mgr -- and level = 1

Data Set

Emp  |  Mgr
-----------
A11  |  A1
A12  |  A1
B1   |  B
C12  |  C1
A1   |  A
C1   |  C
C21  |  C2
A    |  C

Expected ordered result set

Emp  |  Mgr
-----------
A    |  C
A1   |  A
A11  |  A1
A12  |  A1
B1   |  B
C1   |  C
C12  |  C1
C21  |  C2
Vishal
  • 198
  • 1
  • 3
  • 11

1 Answers1

0
select level, emp, mgr
from emp
connect by mgr = prior emp
start with mgr in (select mgr from emp minus select emp from emp)
order by emp, mgr;
ishando
  • 306
  • 3
  • 7
  • Worked to an extent. Now when I added a new record where mgr = emp, it created a loop. I then added nocycle clause, however it's now returning a additional row => [sqlfiddle](http://sqlfiddle.com/#!4/e5462/2). – Vishal Feb 24 '18 at 13:26
  • Well, that just seems like dodgy data - in your sqlfiddle you seem to have multiple ways of marking employees as not having a manager (null, 'root', mgr = emp), pick one and stick with it – ishando Feb 24 '18 at 22:40
  • Well root is valid a case, I added it for better clarity and hierarchy visibility. For now we can ignore mgr is null, as it was there just to validate wider test cases. Other case where mgr = emp is a valid case, and the query is not working for this case. – Vishal Feb 26 '18 at 07:35