Scenarios andsSteps “get last change” problem

I have following table abstracting some scenarios and their steps ordered by START_DATE “desc”.

SCENARIO_KEY	STEP_KEY	START_DATE	END_DATE	PREVIOUS_SCENARIO_KEY	PREVIOUS_STEP_KEY
128	44	20xx04x4	0	120	44
120	44	20xx03x3	20xx04x4	120	38
120	38	20xx11x0	20xx03x3	121	38
121	38	20xx07x2	20xx11x0	120	44
120	44	20xx07x1	20xx07x2	120	38

I need to get first scenario_key (or another identifier) after last change to step with value 44 (step_key = 44).

So the result of this query should be some identifier of the last change – START_DATE of the first row with value 44 after last change (20xx03x3) or SCENARIO_KEY after last change (120).

In short, I need to be able to identify the line of last change to “44” step key.

Is there any analytical function (or another query) to achieve this? Can you advice?

Answer

Hmmm . . . you can get the earliest 44 date after the last non-44 date using:

select min(start_date)
from t
where t.start_date > (select max(t2.start_date)
                      from t t2
                      where t2.step_key <> 44
                     );

I think this is what you are asking for.

You can use window functions too. Assuming that the most recent row is “44”:

select t.*
from (select t.*,
             row_number() over (order by start_date) as seqnum,
             row_number() over (partition by step_key order by start_date) as seqnum_sk
      from t
     ) t
where step_key = 44 and seqnum = seqnum_sk
order by start_date
fetch first 1 row only;