Running (cumulative) MIN and MAX

The running (so-far) minimum or maximum up to each row — e.g. a stock’s lowest and highest price to date, on every row. Same window-function syntax across all six dialects (MySQL 8+ and SQLite 3.25+ for window support).

SELECT
  d,
  price,
  MIN(price) OVER (ORDER BY d
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_min,
  MAX(price) OVER (ORDER BY d
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max
FROM prices
ORDER BY d;

Per group, add PARTITION BY:

SELECT
  symbol, d, price,
  MIN(price) OVER (PARTITION BY symbol ORDER BY d ROWS UNBOUNDED PRECEDING) AS running_min,
  MAX(price) OVER (PARTITION BY symbol ORDER BY d ROWS UNBOUNDED PRECEDING) AS running_max
FROM prices;

A running max never decreases and a running min never increases — a cheap sanity check when debugging a query: if running_max drops between two consecutive rows, something upstream is wrong (a bad PARTITION BY, or rows out of order).

Ties in ORDER BY make the per-row result order-dependent. Unlike a running SUM (see the running-total recipe), the final running min/max value is unaffected by whether tied rows are grouped together — but which physical row a tie’s running value first appears on is not guaranteed unless the tie is broken. If several rows share the same d, add a tiebreaker column (ORDER BY d, id) so every dialect returns the same row order and the same per-row running values on repeated runs. As with any windowed aggregate, always spell out the ROWS frame explicitly — the default frame when you write only ORDER BY (no ROWS/RANGE) is RANGE UNBOUNDED PRECEDING, which computes the same min/max for every row that ties on the order column.