title: How to find max level in each path in a Oracle hierarchical SQL?
body: <p>I would like to know the way to find the max level number in Oracle hierarchical SQL within the given path.</p>
  
  <p>For example : If connect by clause starts with root 1 having below relation .</p>
  
  <pre><code>parent_id node_id votes
   NULL    1        -
   1       2        10 
   2       3        12 
   3       4        11
   1       20        5
   20      30       20
   20      40        4
   40      50       22
  </code></pre>
  
  <p>Here first 3 records belongs to one path with max levle 3.
  Next 2 records belong to another path with max level 2.
  Last two record belongs to another path with max level 3.</p>
  
  <p>I need output with these max level within the given distinct path and minimum votes:</p>
  
  <pre><code>parent_id  node_id LEVEL  MAX_LEVL  MIN_VOTE
     1       2         1        3        10
     2       3         2        3        10
     3       4         3        3        10
     1       20        1        2         5
     20      30        2        2         5
     1       20        1        3         4
     20      40        2        3         4 
     40      50        3        3         4 
  
  
  
       1
       |
  --------------
  |           |
  2           20              
  |           |
  3     --------------
  |     |             |
  4     30            40
                      |
                     50
  </code></pre>
  
  <p>Thanks,
  Guru</p>