Rolling MySQL function to provide max value up to each row in an ordered column

user_2476348 0
Viewed 26

I'm trying to grab the max value in a column up to and including the current row. In the xample below, I've ordered the table by date. I would like to calculate the value of rollingMaxSource as shown. Starting from the top, it reports the max value found up to that point. I then compare the max value to the source value. If source is lower than max, I flag it, since with this ordering the value of source should only be equal to or higher than the previous row.

Can this be done in MySQL?

Account Source rollingMaxSource flag date
1 10 10 false 2022-05-01
1 10 10 false 2022-06-01
1 9 10 true 2022-07-01
1 10 10 false 2022-08-01
1 11 11 false 2022-09-01
mysql
1 Answers

Yes, this can be done with windowing functions, which requires MySQL 8.0:

SELECT Account, Source, rollingMaxSource,
  Source < rollingMaxSource AS flag,
  date
FROM (
  SELECT Account, Source, date,
    MAX(Source) OVER (ORDER BY date) AS rollingMaxSource
  FROM mytable
) AS t;

This works when MAX() is used as a window function because the default frame includes from the earliest row to the current row.

Bill_Karwin_208600
Related