1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Parent-child hierarchy -highest level, count of levels.

Discussion in 'SQL PL/SQL' started by sania, May 11, 2018.

  1. sania

    sania Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    india
    Hi friends,
    I have a table ancestor, which has locations in a parent-child hierarchy. eg; abc1-0001,abc1-0001-ab,abc1-0001-ab-01 etc, where abc1-0001 is parent for abc1-0001-ab, and it is parent for abc1-0001-ab-01 and so on...
    I have written the below query to fetch the levels.

    SELECT ancestor,lcation,connect_by_iscycle "cycle", LEVEL as abc,SYS_CONNECT_BY_PATH(ancestor, '//') "path" FROM max.ancestor CONNECT BY nocycle PRIOR ancestor = lcation.


    I want query to find the parent with highest number of levels(child) and count of levels for each parent.

    Please help.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Provide create table statements and sample data.
     
  3. sania

    sania Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    india
    please find below-

    NOPARALLELCREATE TABLE max.ancestor
    (
    lcation VARCHAR2(30 CHAR) NOT NULL
    , ANCESTOR VARCHAR2(30 CHAR) NOT NULL
    , sysid VARCHAR2(12 CHAR) NOT NULL
    , steid VARCHAR2(8 CHAR) NOT NULL
    , orggid VARCHAR2(8 CHAR) NOT NULL
    , ancestorID NUMBER NOT NULL
    , ROWSTAMP VARCHAR2(40 CHAR) NOT NULL
    )

    sample data-

    upload_2018-5-11_20-41-13.png
     
  4. sania

    sania Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    india

    hi Zargon,

    can you please suggest on the question. //i have provided sample data nd create stmt.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    parents at what level do you need to compare?
    also,provide here INSERT statements for you table.