0

I'm trying to get a query to "follow" the max values in each level excluding the max values in other branches.
Here some examples to illustrate what I'm trying to achieve:

Given this hierarchical structure:

    38
      15
        10
        5
          2
          3
      17
        9
        8
      6
    26
      13
      1
      12
    18
      11
      7

I would like to get:

    38
      17
        9

and not:

    38
      17
        10
          3

Like I would get by using the sql query I've come up with until now:

select lpad(' ',2*(level-1))|| max(child)
from test_connect_by  
start with parent is null
connect by prior child = parent
group by level

That get the absolute max from each level without excluding the other branches on each iteration over the levels.

The example data comes from http://www.adp-gmbh.ch/ora/sql/connect_by.html

Thanks in advance for any hint
Vlax

  • 1
    What if you used: select lpad(' ',2*(level-1)) || child from (select parent, max(child) as child from test_connect_by group by parent) t start with with parent is null connect by prior child = parent; – ypercubeᵀᴹ Oct 28 '14 at 16:52
  • @ypercube that seems to be the answer to my woes, please put it as an answer so I can mark it as the correct answer. Thank you very much! – Vlax Oct 29 '14 at 9:31
0

This seems to solve the question asked. Not sure if there is a solution without aggregating first:

select lpad(' ',2*(level-1)) || child as result
from 
  ( select parent, max(child) as child 
    from test_connect_by 
    group by parent
  ) t 
start with parent is null 
connect by prior child = parent
order by level ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.