Second greatests running value with SQL

I have a table such as:

n	value
1	1
2	4
3	4
4	6
5	4
6	8

I can easily find the running max of this value:

SELECT *, max(value) over (order by n rows unbounded preceding) as mx
FROM table

n	value	mx
1	1	1
2	4	4
3	4	4
4	6	6
5	4	6
6	8	8

How can I get the second largest sliding number? so output is one such:

n	value	second_mx
1	1
2	4	1
3	4	4
4	6	4
5	4	4
6	8	6

p.s:

SELECT *,nth_value(value,2) over (order by n rows unbounded preceding) as second_mx
FROM table

Do not work, because the order by n describes how to sort the nth.

Answer

I’m providing a separate answer because this one is so different (and I may delete the other answer). I am convinced this can be handled only with window functions. And I think this offers a solution.

This starts with a bunch of explanation. You can skip down for the query and link to db<>fiddle.

There are two cases where the second max is really simple:

• If the current value is the maximum value and it has appeared before, then it is the second max.
• If the current value is the maximum value and it has never appeared, then the previous maximum is the second max.

An additional simple case:

• If the value is less or equal to the previous second max, the second max does not change.

And finally, an important property of the second max:

• The second max is increasing.

So, the idea is to do the following:

1. Calculate the “easy” cases.
2. Assign the second in the “trivial” cases, when it doesn’t change based on the easy cases.
3. Assign the current value in the rest.
4. Do a cumulative max of the second sum.

This results in:

select t.*, max(imputed_second_max) over (order by n) as second_max
from (select t.*,
             (case when sometimes_mx_2 is not null then sometimes_mx_2
                   when value <= max(sometimes_mx_2) over (order by n) then max(sometimes_mx_2) over (order by n)
                   else value
              end) as imputed_second_max
      from (select t.*,
                   (case when value = mx and nth_value > 1 then value
                         when value = mx and nth_value = 1 then lag(mx) over (order by n)
                    end) as sometimes_mx_2
            from (select t.*, max(value) over (order by n) as mx,
                         row_number() over (partition by value order by n) as nth_value
                  from t
                 ) t
           ) t
      ) t
order by n;

I found that I needed to augment the test cases to get better coverage. I found that decreasing sequences are particularly tricky.

Here is a db<>fiddle.