Replicate CONNECT_BY_ROOT 10g Functionality

[sonnysavage]

1) I am new to using hierarchical queries, so bear with my ignorance.

2) I need to be able to find the root node for any given node. There aren't any cyclical heirarchies, so the solution can ignore this possibility. The primary key column is labeled 'ID' and the hierarchical column name is 'PARENT_ID'.

Let me know if my question needs clarification.

Thanks!

 

[sonnysavage]

Here's what I've found so far, and it works. Am I doing this the best way?

SELECT id,
	type
FROM grp_groups
WHERE LEVEL = (
		SELECT MAX ( LEVEL )
		FROM grp_groups
		START WITH id = 31
		CONNECT BY PRIOR parent_id = id	
	)
START WITH id = 31
CONNECT BY PRIOR parent_id = id

 

[sonnysavage]

Well, looks like no-one else is knowledgeable on this issue. I found another method of this. I can't detect a performance difference between the two.

SELECT *
FROM (
		SELECT id,
			type
		FROM grp_groups
		START WITH id = 31
		CONNECT BY PRIOR parent_id = id
		ORDER BY LEVEL DESC
	)
WHERE ROWNUM = 1