Scenarios andsSteps "get last change" problem

Question

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 1

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;