Course contentsShow
← Data Science
Lesson 1018 of 2,14526. Advanced SQLFree lesson

Running MIN and MAX Values

Track minimum and maximum values up to each row using MIN() and MAX() window functions.

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.