I'm looking to create the "fastest_run_time" column in PostgreSQL by looking at what the "current" personal best is as of the month of that row. So for example:

• In 2016-07 my personal best was 762, it was beaten by a 720 in 2016-08
• Since the run on 2016-09 of 745 isn't an improvement on 720, the fastest_run_time should stay as 720
• It's only updated again when it is beaten with a 691 in 2016-12.

I've tried doing some partitioning and max/mins and have got it into this format but can't really see where to go from here

CodePudding user response：

if the partition by syntax is supported:

select mt.*,
min(run_time) over 
        (partition by run_type 
         order by period 
         rows between unbounded preceding and current row) as fastest_run_time 
from mytbl mt

CodePudding user response：

Just a subquery:

select run_type, to_char(period, 'YYYY-MM'), run_time, (
    select min(rs.run_time) from run rs
        where rs.period <= run.period
) fastest_run_time from run;

Demo with schema.

Result: