title: PL-SQL TREE MAX VALUE ROLLUP
body: <p>Sorry if this question is already asked. please redirect me.</p>
  
  <p>Sorry if it is on hold. 
  <BR>
  Just to clarify my question.</p>
  
  <p>Here is my details. </p>
  
  <p>I have 1 view named T1 and it has 3 columns named ID(NUM), PARENT_ID(NUM) and COLOR(NUM). </p>
  
  <p>*ID column is the child of column PARENT_ID<br>
  *COLOR column is the assigned color of ID column (4000 highest, 3000, 2000, 1000 lowest)<br>
  *Row with ID is 1 and PARENT ID 0 is the topmost/root level. <br>
  *I have no row with ID is 0;</p>
  
  <p>My problem is, I do not know how to: <br>
   1. change the parent color according to its children's highest color value. <br> 
  eg: row with id 2 must be 4000 color (4000[row id 4]>3000[row id 5]). <br>
   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.<br></p>
  
  <pre><code>ID  parent ID       color
  1     0                1000
  2     1                1000
  3     1                2000
  4     2                4000
  5     2                3000
  </code></pre>
  
  <p>output should be.</p>
  
  <pre><code>id  color
  1   4000
  2   4000
  3   2000
  4   4000
  5   3000
  </code></pre>
  
  <p>I am using Oracle 11g.</p>
  
  <p>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.<br></p>
  
  <p>Please advice..</p>