Distance from maximum value for each distance

select dst, val,        max(case when flag is null then dst end) over (order by dst)          as first_occurrencefrom   (         select dst, val,                 case when val <= max(val) over (order by dst                              rows between unbounded preceding and 1 preceding)                then 1 end as flag         from tmp_maxval       )order by dst;       DST        VAL FIRST_OCCURRENCE---------- ---------- ----------------         1          3                1         2          2                1         3          1                1         4          2                1         5          4                5         6          2                5         7          2                5         8          5                8         9          5                8        10          1                8

Or, if you are on Oracle version 12.1 or higher, MATCH_RECOGNIZE can do quick work of this assignment:

select dst, val, first_occurrencefrom   tmp_maxval tmatch_recognize(  order by dst  measures a.dst as first_occurrence  all rows per match  pattern  (a x*)  define   x as val <= a.val)order by dst;

You could use either max() or min() combined with case when:

select t.*,        min(case when val = mv then dst end) over (partition by mv order by dst) v1,       max(case when val = mv then dst end) over (partition by mv order by dst) v2  from (select t.*, max(val) over (order by dst) mv from tmp_maxval t) t   order by dst

Result:

       DST        VAL         MV         V1         V2---------- ---------- ---------- ---------- ----------         1          3          3          1          1         2          2          3          1          1         3          1          3          1          1         4          2          3          1          1         5          4          4          5          5         6          2          4          5          5         7          2          4          5          5         8          5          5          8          8         9          5          5          8          9        10          1          5          8          9

Explained logic and words first occurence suggest that you need min(), but third row in your example suggest max() ;-) In data which you provided you can observe difference in rows 9-10. Choose what you want.

You can get the maximum value using a cumulative max:

select mv.*, max(mv.value) over (order by mv.distance) as max_valuefrom ap_risk.tmp_maxval mv;

I think this answers your question. If you want the distance itself:

select mv.*,       min(case when max_value = value then distance end) over (order by distance) as first_distance_at_max_valuefrom (select mv.*, max(mv.value) over (order by mv.distance) as max_value      from ap_risk.tmp_maxval mv     ) mv;