Connect by clause to get the top of hierarchy

问题:

I am facing an issue while using the CONNECT BY clause in Oracle for finding hierarchical data. Let me give an example: A is my parent part which has child part B and B also has a child part C. When I am using the CONNECT BY clause I am able to get all the three levels but I only want the top most level, i.e. A.

回答1:

Oracle has a LEVEL pseudocolumn that you can use:

SELECT
  myTable.ID,
  myTable.ParentID
FROM myTable
WHERE LEVEL = 1
CONNECT BY PRIOR myTable.ID = myTable.ParentID

---

To find a top-level (root) value from any level, precede the column name with the CONNECT_BY_ROOT operator:

SELECT
  myTable.ID,
  myTable.ParentID,
  CONNECT_BY_ROOT myTable.ID AS "Top Level ID"
FROM myTable
CONNECT BY PRIOR myTable.ID = myTable.ParentID

回答2:

I am adding this solution for tables with one or multiple trees (hierarchical data).

Starting with one node (row) somewhere in the tree (hierarchical data), wanting to find the top node (root).

The query is taking advantage of the fact that the ONLY the top node (root) of a tree don't have a parent, which is a very common attribute of the top node (root) in any tree structure.

SELECT
  c.id
FROM 
  node c
WHERE
  c.parent_id is null
CONNECT BY PRIOR
  c.parent_id = c.id
START WITH
  c.id = 1059002615

回答3:

SELECT * FROM (
  SELECT CONNECT_BY_ROOT myTable.ID AS "Top Level ID"
  FROM myTable
  CONNECT BY PRIOR myTable.ID = myTable.ParentID
)
WHERE myTable.ParentID IS NULL