Running MIN and MAX Values
What You'll Learn
You'll learn how to track the smallest and largest values encountered so far as you move through an ordered dataset using MIN() and MAX() window functions.
The Core Idea
A running minimum or running maximum shows the lowest or highest value seen up to and including the current row. Think of it like tracking temperature records: as you read through daily temperatures chronologically, the "record low" or "record high" only changes when you encounter a new extreme value—otherwise, it stays the same.
Using window functions with cumulative frames, you can compute these running extremes without self-joins or complex subqueries.
Basic Syntax
SELECT
date,
temperature,
MIN(temperature) OVER (ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS running_min,
MAX(temperature) OVER (ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS running_max
FROM weather_data;
The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the first row up through this row"—giving you the minimum or maximum value encountered so far.
Practical Example
SELECT
employee_id,
salary,
hire_date,
MIN(salary) OVER (ORDER BY hire_date) AS lowest_salary_so_far,
MAX(salary) OVER (ORDER BY hire_date) AS highest_salary_so_far
FROM employees
ORDER BY hire_date;
This tracks salary extremes as employees are hired over time, useful for analyzing compensation trends.
Common Pattern: Shorthand
When using ORDER BY without an explicit frame, many databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which often produces the same result for running MIN/MAX.
Key Takeaway: Use MIN() and MAX() as window functions with cumulative frames to track the lowest and highest values encountered up to each row, perfect for identifying record-breaking values or monitoring thresholds over time.