Hierarchical query with min value calculated at each level in Oracle

Vote count:

0

I've been working on this problem on and off for a few days now, and I'm getting nowhere.

I have a dataset that resembles this:

NAME                EXPIRATION          PARENT_ID    CLASS_ID           
Master Class           365                              1               
Second Class           366                  1           2                 
Third Class            355                  2           3                 
Fourth Class           1001                 2           4                 
Fifth Class            1000                 4           5
Sixth Class            999                  4           6  

ect. ect.

I can use a hierarchical query to get a view of what classes are required under a certain class.

What I really want to know is what the minimum expiration date is for the current level and sub-levels of the hierarchy. The expiration is the minimum expiration of anything under it.

If I wanted to do this in a brute force manner, I could get the results of my hierarchical query back, then for each line run a query that looks like this:

select min(expiration_date)
from ( start with class_id = $EACH_CLASS_ID_FROM_PREVIOUS_QUERY
connect by prior required_class_id = class_id);

I'm picturing the result like this:

NAME             EXPIRATION                           CLASS_ID
Master Class      355 (Min of it or anything under it)     1
Second Class      355  ""                                  2
Third Class       355  ""                                  3
Fourth Class      999  ""                                  4
Fifth Class       1000 ""                                  5
Sixth Class       999  ""                                  6

I'm assuming there's a better way though? Maybe?

Thanks for any help, I've been puzzling over this for a few days now.

asked 1 min ago

user210099

527