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!

Numering the siblings

Discussion in 'SQL PL/SQL' started by kiran.marla, Sep 3, 2013.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Code (SQL):
    SELECT LEVEL, LPAD (' ', (LEVEL - 1) * 3) || NAME report
    FROM emp
    START WITH manager_id IS NULL
    CONNECT BY manager_id = PRIOR emp_id;
     
         LEVEL REPORT
    ---------- ------------------------------
             1 JACK
             2    MILLS
             3       ROGERS
             3       DAVIDS
             3       WHITE
             2    ATIL
             3       BROWN
             3       LEE
     
    8 ROWS selected.
    In the above example level 3 contains ROGERS, DAVIDS, WHITE. My requirement is to add one more column SNO and start give numbering to these
    like

    LEVEL SNO NAME
    3 1 ROGERS
    3 2 DAVIDS
    3 3 WHITE

    Help is appreciated...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is one way to do it:

    Code (SQL):
    SQL> CREATE TABLE tmp_emp (lvl NUMBER,
      2        rpt_level NUMBER,
      3        report varchar2(4000));
     
    TABLE created.
     
    SQL>
    SQL> DESC tmp_emp;

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     LVL                                                NUMBER
     RPT_LEVEL                                          NUMBER
     REPORT                                             VARCHAR2(4000)
     
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> DECLARE
      2        rpt_level NUMBER:=0;
      3        prev_rpt_level NUMBER:=1;
      4  
      5        cursor report_levels IS
      6        SELECT LEVEL lvl,
      7        LPAD (' ', (LEVEL - 1) * 3) || NAME report
      8        FROM emp
      9        START WITH manager_id IS NULL
     10        CONNECT BY manager_id = PRIOR emp_id;
     11  BEGIN
     12        FOR rptrec IN report_levels loop
     13         IF rptrec.lvl = 1 THEN
     14          rpt_level := 1;
     15          prev_rpt_level := rptrec.lvl;
     16         elsif rptrec.lvl <> prev_rpt_level THEN
     17          rpt_level := 1;
     18          prev_rpt_level := rptrec.lvl;
     19         elsif rptrec.lvl = prev_rpt_level THEN
     20          rpt_level:= rpt_level+1;
     21          prev_rpt_level := rptrec.lvl;
     22         END IF;
     23  
     24         INSERT INTO tmp_emp
     25         VALUES(rptrec.lvl, rpt_level, rptrec.report);
     26  
     27  --       dbms_output.put_line(rptrec.lvl||'      '||rpt_level||' '||rptrec.report);
     28  
     29        END loop;
     30  
     31  END;
     32  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT lvl,
      2       CASE WHEN lvl = 3 THEN rpt_level ELSE NULL END report_level,
      3       report
      4  FROM tmp_emp;
     
           LVL REPORT_LEVEL REPORT                                                  
    ---------- ------------ ---------------------------------------------          
             1              KING                                                    
             2                 JONES                                                
             3            1       SCOTT                                            
             4                       ADAMS                                          
             3            1       FORD                                              
             4                       SMITH                                          
             2                 BLAKE                                                
             3            1       ALLEN                                            
             3            2       WARD                                              
             3            3       MARTIN                                            
             3            4       TURNER                                            
             3            5       JAMES                                            
             2                 CLARK                                                
             3            1       MILLER                                            
     
    14 ROWS selected.
     
    SQL>
    I have had no luck using any analytic functions to generate a similar numbering.
     
    kiran.marla likes this.
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hi,

    Here is a solution which allows to enumerate uniquely each child within its parent sub-tree regarding the alphanumeric ordering of those children's first name within their parent sub-tree (within each level)

    Code (SQL):

    SET SQLBLANKLINES ON;

    COLUMN lvl FORMAT 9999;
    COLUMN report FORMAT a50;
    COLUMN sno FORMAT 9999;
    COLUMN rn FORMAT 9999;


    SELECT  t3.lvl      ,
            t3.sno      ,
            t3.report  
    FROM
    (
        SELECT  t2.lvl      ,
                t2.report   ,
                t2.empHierarchy,
                ROW_NUMBER()
                    OVER
                    (
                        PARTITION BY t2.manager_id
                        ORDER BY t2.report
                    ) sno
        FROM
        (
            SELECT  level lvl ,
                    nvl(t1.manager_id, 0) manager_id,
                    lpad(' ', 3 * (level - 1), ' ') || t1.first_name report,
                    sys_connect_by_path(t1.first_name, '/') empHierarchy
            FROM    hr.employees t1
            START WITH t1.manager_id IS NULL
            CONNECT BY PRIOR t1.employee_id = t1.manager_id
        ) t2
    ) t3
    ORDER BY t3.empHierarchy;
     
    And the output would be (the second column SNO)

    Code (Text):

     LVL    SNO REPORT
    ----- ----- --------------------------------------------------
        1     1 Steven
        2     1    Adam
        3     1   Alexis
        3     2   Anthony
        3     3   James
        3     4   Julia
        3     5   Laura
        3     6   Mozhe
        3     7   Nandita
        3     8   TJ
        2     2    Alberto
        3     1   Amit
        3     2   Clara
        3     3   Danielle
        3     4   David
        3     5   Mattea
        3     6   Sundar
        2     3    Den
        3     1   Alexander
        3     2   Guy
        3     3   Karen
        3     4   Shelli
        3     5   Sigal
        2     4    Eleni
        3     1   Alyssa
        3     2   Charles
        3     3   Ellen
        3     4   Jack
        3     5   Jonathon
        3     6   Kimberely
        2     5    Gerald
        3     1   Elizabeth
        3     2   Harrison
        3     3   Lisa
        3     4   Sundita
        3     5   Tayler
        3     6   William
        2     6    John
        3     1   Christopher
        3     2   David
        3     3   Nanette
        3     4   Oliver
        3     5   Peter
        3     6   Peter
        2     7    Karen
        3     1   Allan
        3     2   Janette
        3     3   Lindsey
        3     4   Louise
        3     5   Patrick
        3     6   Sarath
        2     8    Kevin
        3     1   Alana
        3     2   Curtis
        3     3   Donald
        3     4   Douglas
        3     5   Kevin
        3     6   Peter
        3     7   Randall
        3     8   Trenna
        2     9    Lex
        3     1   Alexander
        4     1      Bruce
        4     2      David
        4     3      Diana
        4     4      Valli
        2    10    Matthew
        3     1   Girard
        3     2   Irene
        3     3   James
        3     4   Jean
        3     5   Julia
        3     6   Martha
        3     7   Steven
        3     8   Winston
        2    11    Michael
        3     1   Pat
        2    12    Neena
        3     1   Hermann
        3     2   Jennifer
        3     3   Nancy
        4     1      Daniel
        4     2      Ismael
        4     3      John
        4     4      Jose Manuel
        4     5      Luis
        3     4   Shelley
        4     1      William
        3     5   Susan
        2    13    Payam
        3     1   Hazel
        3     2   Jason
        3     3   Jennifer
        3     4   Kelly
        3     5   Ki
        3     6   Michael
        3     7   Randall
        3     8   Timothy
        2    14    Shanta
        3     1   Britney
        3     2   John
        3     3   Joshua
        3     4   Renske
        3     5   Samuel
        3     6   Sarah
        3     7   Stephen
        3     8   Vance

    107 rows selected.

    SQL>

     
     
    kiran.marla and Bharat like this.
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi David and Dari,

    Provided solutions beauty.. Will consider Dari solution because Dari provided sibling numbering for all the levels brilliantly with SQL Query... Thanks David and Dari once again...
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You used an analytic function I forget about, row_number(). As usual I learned something today, and remembered something I tend to forget.

    Thank you. Nicely done.