PL-SQL TREE MAX VALUE ROLLUP

Question

Sorry if this question is already asked. please redirect me.

Sorry if it is on hold.
Just to clarify my question.

Here is my details.

I have 1 view named T1 and it has 3 columns named ID(NUM), PARENT_ID(NUM) and COLOR(NUM).

*ID column is the child of column PARENT_ID
*COLOR column is the assigned color of ID column (4000 highest, 3000, 2000, 1000 lowest)
*Row with ID is 1 and PARENT ID 0 is the topmost/root level.
*I have no row with ID is 0;

My problem is, I do not know how to:
1. change the parent color according to its children's highest color value.
eg: row with id 2 must be 4000 color (4000[row id 4]>3000[row id 5]).
2. Next is, also change grandparents color according to the parents highest color. If grand grand parents exists then it must also update according to grand parents highest color.

ID  parent ID       color
1     0                1000
2     1                1000
3     1                2000
4     2                4000
5     2                3000

output should be.

I am using Oracle 11g.

Please also note that my T1 view is also a result of several tables joined using Connect By. I already managed to get the maximum color using Connect By but it only reflects its immediate parent color and NOT its grand parents color.

Please advice..

Can you clarify what it is you want, and how you're getting the values for 'color' in your desired output? It's not clear to me from the provided sample output. What is "ultimate parent"? Is that id 0? If so, why is it not listed in your table? If it's not, what is it? — Mark J. Bobak, Feb 20 '14 at 09:15
i already managed to get the maximum color of the children but it only reflects its immediate parent and NOT its grand parent color. I tried to use recursion just to update grand parent color but its too slow — Seigfred Mondez, Feb 28 '14 at 01:06

score 0 · Accepted Answer · answered Feb 20 '14 at 09:30

  WITH tab
         AS (SELECT 1 AS ID, 0 AS parent_ID, 1000 AS color FROM DUAL
             UNION
             SELECT 2 AS ID, 1 AS parent_ID, 1000 AS color FROM DUAL
             UNION
             SELECT 3 AS ID, 1 AS parent_ID, 2000 AS color FROM DUAL
             UNION
             SELECT 4 AS ID, 2 AS parent_ID, 4000 AS color FROM DUAL
             UNION
             SELECT 5 AS ID, 2 AS parent_ID, 3000 AS color FROM DUAL)
      SELECT temp_id id, MAX (color) color
        FROM (    SELECT id,
                         parent_id,
                         color,
                         CONNECT_BY_ROOT id temp_id
                    FROM tab
              CONNECT BY PRIOR id = parent_id)
    GROUP BY temp_id
    ORDER BY 1 ASC;

      ID      COLOR
 ---------- ----------
     1       4000
     2       4000
     3       2000
     4       4000
     5       3000

5 rows selected.

Wow!!! That was fast. Thank you so much, I already wasted two days on thinking how to approach it and already on the process on mixing Recursion and Connect By. Hehe Again thank you..!!! — Seigfred Mondez, Feb 21 '14 at 00:32
Just another quick question though, why is it you named id as TEMP_ID ID instead of ID TEMP_ID in the SELECT clause — Seigfred Mondez, Feb 21 '14 at 00:55
@seigfred Mondez:dats the alias for id ,dats it ,jst wanna diffetentiate between two id's — Gaurav Soni, Feb 21 '14 at 01:53

PL-SQL TREE MAX VALUE ROLLUP

1 Answers1