Window Frames: Truly Understanding ROWS vs. RANGE

1. What a window frame actually is

Every window function with ORDER BY operates within a window frame, a subset of the current partition defined relative to the current row. Aggregating window functions like SUM(), AVG() or COUNT() only include the rows inside that frame in their calculation, not the entire partition. Without a solid understanding of the window frame, the behavior of these functions stays hard to predict once sorting changes or ties appear.

SQL offers three different modes for defining a window frame: ROWS, RANGE, and, used less often, GROUPS. All three follow the syntax ROWS/RANGE/GROUPS BETWEEN start AND end, but differ fundamentally in how they determine which rows belong to the frame. This difference is not an academic nuance, it has a direct effect on the query result in certain situations.

This article clarifies the difference between ROWS and RANGE in detail, shows with concrete examples involving ties when both return the same and when they return different results, and places GROUPS as a third option.

2. ROWS: the physical row frame

ROWS defines a window frame purely based on physical row positions, regardless of the actual value in the sort column. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW always includes exactly the two preceding rows plus the current row, no matter what values those rows carry in the sort column and regardless of whether multiple rows share the same sort value. Counting happens strictly by position in the order established by ORDER BY.

This property makes ROWS the right choice whenever a fixed number of records is intended, independent of their values: a 7-day moving average over the last seven records, a running total over the last ten transactions, or a comparison with the three preceding readings. ROWS is deterministic with respect to the number of rows included, which makes the calculation intuitively predictable for developers.

-- ROWS: exactly 3 physical rows in the frame (2 preceding + current)
SELECT
    student_id,
    exam_date,
    score,
    SUM(score) OVER (
        ORDER BY exam_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rows_sum
FROM exam_scores
ORDER BY exam_date;

-- exam_date  | score | rows_sum (always sums exactly 3 rows once available)
-- 2026-01-10 | 70    | 70
-- 2026-01-10 | 85    | 155   <- two rows share the same date, still just 2 rows
-- 2026-01-12 | 90    | 245   <- exactly the 3 preceding physical rows

3. RANGE: the logical value range

RANGE, on the other hand, defines a window frame through a value range of the sort column, not through row positions. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW includes every row whose sort value falls within two units below the current value, regardless of how many physical rows that actually amounts to. With a numeric sort column and a current value of 100, RANGE BETWEEN 2 PRECEDING would include all rows with values between 98 and 100, whether that is one, three, or twenty rows.

The crucial difference from ROWS: with RANGE, every row sharing the same sort value as the current row is automatically treated as one logical group, so-called peers. The CURRENT ROW end of a RANGE frame therefore does not mean "up to the current physical row", it means "up to and including all rows with the same sort value as the current row". This exact difference is the root of most confusion around RANGE.

-- RANGE: frame boundary defined by value distance, not row count
SELECT
    student_id,
    exam_date,
    score,
    SUM(score) OVER (
        ORDER BY exam_date
        RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW
    ) AS range_sum
FROM exam_scores
ORDER BY exam_date;

-- exam_date  | score | range_sum (includes ALL rows within the date range)
-- 2026-01-10 | 70    | 155   <- both same-date rows included together
-- 2026-01-10 | 85    | 155   <- identical result for both peer rows
-- 2026-01-12 | 90    | 245   <- all rows within 2 days back

4. The key difference: how each handles ties

As long as the sort column contains only unique values, ROWS and RANGE with the same boundaries almost always return identical results, because every row occupies exactly one physical position and, simultaneously, a unique value range. But as soon as multiple rows share the same sort value, for example several orders on the same day or several readings at the same second, the results of ROWS and RANGE diverge noticeably.

ROWS treats every row individually by its physical position, so even if two rows have the same sort value, they can end up with different frame boundaries. RANGE, on the other hand, forces all rows with the same sort value into the same frame and therefore the same aggregation result, because they count as peers. That is the core of the difference: ROWS can produce different results for rows with an identical sort value, RANGE guarantees the same result for peers.

-- Side-by-side: ROWS vs. RANGE with duplicate order dates
SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rows_running_total,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS range_running_total
FROM orders
ORDER BY order_date, order_id;

-- order_id | order_date | amount | rows_running_total | range_running_total
-- 1        | 2026-02-01 | 100    | 100                | 250   <- peers already merged
-- 2        | 2026-02-01 | 150    | 250                | 250   <- same peer group
-- 3        | 2026-02-03 | 80     | 330                | 330
-- Rows 1 and 2 share the same order_date and are RANGE peers

5. The invisible default frame and its pitfalls

If an ORDER BY clause is present but no explicit frame clause, the database defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not ROWS. This is a common pitfall, because many developers implicitly expect ROWS semantics but actually get RANGE semantics with its peer rules. With unique sort values this difference goes unnoticed, but with ties it leads to results that look wrong at first glance while exactly matching the SQL standard.

For exactly this reason, it is recommended to spell out the frame clause explicitly for every production window function, instead of relying on the default. Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead of the implicit RANGE variant immediately makes the intended behavior visible and prevents a later reader of the code from assuming the wrong behavior.

6. GROUPS: the third, less commonly used option

Alongside ROWS and RANGE, the SQL standard defines a third frame type, GROUPS, which is available in PostgreSQL from version 11 onward and in a few other modern databases. GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW counts neither rows nor value ranges, but peer groups: every row with the same sort value forms one group, and the number before PRECEDING indicates how many such groups are included, regardless of how many physical rows each group contains.

GROUPS is useful when a fixed number of distinct values is needed instead of a fixed number of rows or a value range, for example "the last three distinct order days" instead of "the last three orders" or "the last three calendar days". In practice, GROUPS is used considerably less often than ROWS and RANGE, but it is the most precise choice for exactly this specific requirement.

-- GROUPS: count 2 preceding PEER GROUPS, not rows or a value range
SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS groups_sum
FROM orders
ORDER BY order_date, order_id;

-- order_date has duplicates: two orders share 2026-02-01
-- The frame always covers exactly 3 distinct order_date groups
-- regardless of how many physical rows each date contains
-- PostgreSQL 11+, some other databases support GROUPS directly

7. RANGE with date and time intervals

A legitimate and common use case for RANGE is working with true calendar windows instead of row windows. RANGE BETWEEN INTERVAL '7 day' PRECEDING AND CURRENT ROW includes every row whose date falls within the last seven calendar days, regardless of how many rows exist on individual days. With time series that have gaps or several events per day, this produces exactly the desired result of a true seven-day window, while ROWS BETWEEN 6 PRECEDING can cover more or fewer than seven calendar days depending on the underlying data.

This date and time interval capability of RANGE is not implemented identically across every database. PostgreSQL and SQL Server support INTERVAL expressions directly inside the RANGE clause, while MySQL before version 8.0.28 was more limited here and partly required numeric offsets instead of true intervals. Before using this in production, it is worth checking the documentation of the specific database for which expression forms are supported for RANGE boundaries.

-- RANGE with a true 7-day calendar window, gaps and multiple
-- events per day handled correctly
SELECT
    sensor_id,
    reading_time,
    temperature,
    AVG(temperature) OVER (
        PARTITION BY sensor_id
        ORDER BY reading_time
        RANGE BETWEEN INTERVAL '7 day' PRECEDING AND CURRENT ROW
    ) AS avg_last_7_calendar_days
FROM sensor_readings
ORDER BY sensor_id, reading_time;
-- Covers exactly 7 calendar days back, regardless of how many
-- readings exist on each individual day

8. Practical example: when the choice actually changes the result

A concrete real-world example: a sales report should show a running total of daily revenue. If several transactions of the same day are stored as separate rows, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW returns the same running total for every row of that day, namely including all transactions of that day, while ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW keeps counting the running total row by row within the same day. For a report meant to show "cumulative revenue through today", RANGE is correct, for a report meant to reflect transaction-precise ordering, ROWS is correct.

The right choice therefore does not depend on a blanket recommendation, but on the business question behind the query. If unsure, ask yourself: "should rows with the same sort value always get the same result?" If the answer is yes, RANGE is the right choice. If the answer is no, because every physical row should be treated individually, ROWS is correct.

9. ROWS and RANGE compared directly

In summary, ROWS and RANGE differ fundamentally in exactly one point: the definition of what "inside the window" means. ROWS answers that with "the nth physical row before or after", RANGE answers that with "every row whose sort value falls within this range, including all rows with a value identical to the current row". With unique sort values, this difference is invisible, with ties it becomes the deciding factor for the correctness of a query.

For practice, this means: if the sort column is guaranteed unique, for example a primary key or a timestamp with microsecond precision, the choice between ROWS and RANGE usually doesn't matter for the result, though it still matters for readability and performance, because ROWS executes more efficiently in most databases. If the sort column is not unique, for example a plain date without time, the choice must be made deliberately based on the business requirement.

10. Summary

ROWS and RANGE define a window frame in two fundamentally different ways: ROWS counts physical row positions, RANGE looks at logical value ranges of the sort column and treats rows with an identical sort value as peers with a guaranteed identical result. As long as the sort column contains unique values, both return the same result, with ties they can differ noticeably.

The invisible default frame when ORDER BY is present without an explicit frame clause is RANGE, not ROWS, which is a common pitfall. Anyone who sets the frame clause consciously and explicitly for every window function, instead of relying on the implicit default, avoids most of the surprises that can arise from the difference between ROWS and RANGE.

11. FAQ: ROWS vs. RANGE in Window Frames