Connect by clause to get the top of hierarchy

oracle connect-by
user2133005 picture user2133005 · Aug 7, 2013 · Viewed 14.6k times · Source

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.

Answer

Ed Gibbs picture Ed Gibbs · Aug 7, 2013

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

Related questions

Why does CONNECT BY LEVEL on a table return extra rows?

Using CONNECT BY LEVEL seems to return too many rows when performed on a table. What is the logic behind what's happening? Assuming the following table: create table a ( id number ); insert into a values (1); insert into a values (2); insert …

Read More
What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

In Oracle, if I have a table defined as … CREATE TABLE taxonomy ( key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY, value VARCHAR2(255), taxHier NUMBER(11) ); ALTER TABLE taxonomy ADD CONSTRAINT taxTaxFkey FOREIGN KEY (taxHier) REFERENCES tax(key); With these values … key …

Read More
Oracle 10g PL/SQL Connect By Prior returning Child and Parent on same row

Table1: Child Parent a Bob Chris 2 Chris Kate 1 Shane Lana 3 Nala Bob 4 Table2: b Talent 1 'something' 2 'nothing' 3 'something' 4 'nothing' SELECT Child FROM Table1 INNER JOIN Table2 ON (Table1.a = Table2.b) WHERE Table2.Talent = 'something' connect by prior Table1.Child = …

Read More