CODEWITHSUNDEEP
sqloracle-database
username
username

Reputation: 57

Determine hierarchical relationship, but in reverse

Consider the following table in Oracle

sortOrder           thisID              levelNo
------------------- ------------------- ---------------------
1                   A                   0
2                   B                   1
3                   C                   1
4                   D                   2
5                   E                   3
6                   F                   3
7                   G                   1
8                   H                   0
9                   I                   1

Which could be seen visually as

A
 B
 C
  D
   E
   F
 G
H
 I

How could I determine the child parent relationship, to output the following below? The relationship is based on the sortOrder and levelNo.

thisID              parentID
------------------- ---------------------
A                   A
B                   A
C                   A
D                   C
E                   D
F                   D
G                   A
H                   H
I                   H

I am familiar with using queries to determine the level based on a hierarchical parent-child relationship, but haven't figured out a way to do it in reverse.

Upvotes: 1

Views: 793

Answers (1)

Abecee
Abecee

Reputation: 2393

Please try

SELECT
  T.thisID
  , CASE T.levelNo 
    WHEN 0 
         THEN T.thisID
    ELSE (
           SELECT thisID FROM Table1
           WHERE sortOrder = (
                               SELECT MAX(sortOrder) FROM Table1 
                               WHERE (levelNo = T.levelNo -  1) 
                                 AND sortOrder < T.sortOrder
                             )
         )
  END parent
FROM Table1 T
ORDER BY sortOrder;

See it in action: SQL Fiddle.

Please comment if and as further detail / adjustment is required.

Upvotes: 1

Related Questions