CODEWITHSUNDEEP
sqloracle-databaseconnect-by
cbm64
cbm64

Reputation: 1119

START WITH....CONNECT BY PRIOR

Does anybody know of a way to know the depth of the CONNECT BY? For example if I had;

PARENT_ID   PARENT_KEY  CHILD_ID    CHILD_KEY
1           BOB         2           CHRIS
1           BOB         3           TODD
2           CHRIS       4           JON
2           CHRIS       5           ALF
5           ALF         6           GERARD
5           ALF         7           MIKE
3           TODD        8           ANN
3           TODD        9           FRED

The depth of CHRIS is 3 and the depth of TODD is 2, How would I find out the depth of CHRIS and TODD programatically where;

START WITH PARENT_KEY = 'BOB' CONNECT BY PRIOR CHILD_ID = PARENT_ID

Sorry, I was being stupid...here is the answer for those who have brain lapses in the future;

SELECT
PARENT_KEY
, MAX(LEVEL) AS DEPTH
FROM TIMELINE_CONFIG
START WITH PARENT_KEY = 'BOB' CONNECT BY PRIOR CHILD_ID = PARENT_ID
GROUP BY PARENT_KEY

Upvotes: 0

Views: 788

Answers (2)

cbm64
cbm64

Reputation: 1119

Sorry, I was being stupid...here is the answer for those who have brain lapses in the future;

SELECT
PARENT_KEY
, MAX(LEVEL) AS DEPTH
FROM TIMELINE_CONFIG
START WITH PARENT_KEY = 'BOB' CONNECT BY PRIOR CHILD_ID = PARENT_ID
GROUP BY PARENT_KEY

Upvotes: 0

Corrado Piola
Corrado Piola

Reputation: 869

You can use the LEVEL pseudo-column:

SELECT ..., LEVEL, ...
FROM ...

Upvotes: 4

Related Questions