Window Frames in SQL: ROWS, RANGE, and GROUPS

Every window function operates within a window, but not every window function reads the frame. The frame is the specific subset of rows a frame-sensitive function is allowed to see when computing a value for the current row, and most of the time it is chosen for you by a default you never wrote. That default is why running totals, moving averages, and one famously surprising LAST_VALUE() result all behave the way they do. This article makes the frame visible: what it is, how ROWS, RANGE, and GROUPS define it, and how to take control of it deliberately.

The Hidden Part of Every Window

A window definition inside OVER (...) can contain three conceptual pieces, and most people only ever write two of them. PARTITION BY divides the rows into independent partitions for the calculation; ORDER BY sequences the rows within each partition; and then there is a third, optional piece, the frame clause, that decides, for the row being calculated right now, exactly which of the ordered rows are in view. The frame clause only means anything for the functions that read it, and its available syntax depends on the engine.

The frame matters only for functions that are frame-sensitive: aggregates used as window functions (SUM, AVG, COUNT, MIN, MAX) and the value functions FIRST_VALUE, LAST_VALUE, and NTH_VALUE. Ranking functions like ROW_NUMBER and offset functions like LAG ignore the frame entirely. But for the functions that do read the frame, understanding it is the difference between a running total and a grand total, or between a moving average and a flat one.

The Sample Dataset

We use a small table of monthly revenue. Two deliberate pairs of equal values (February and March both at 1200.00, April and May both at 1500.00) are included on purpose: those ties, or peers, are exactly where ROWS, RANGE, and GROUPS stop agreeing with one another, which is the whole point of comparing them. The id column gives us a guaranteed-unique value to order by when we need the sequence to be fully deterministic.

monthly_sales

The Default Frame: the One You Never Wrote

The most important thing to understand about frames is that you almost always get one whether you ask for it or not. The rule turns on a single condition: is there an ORDER BY inside the OVER clause?

• No ORDER BY → the default frame is the entire partition. Every row sees every other row, so an aggregate returns the same value on all of them: a flat partition total.
• With ORDER BY → the default frame runs from the start of the partition up to the current row (and its peers). Each row sees a little more than the row before it, so an aggregate grows: a running total.

That second case is why simply adding ORDER BY to a SUM() quietly turns it into a running total. Nothing else changed (you didn't write a frame), but the default frame changed underneath you:

Each running_total is the sum of every revenue from January up to and including the current month. Because month values are all distinct, there are no peers to worry about here, and the total simply grows one month at a time. Written out in full, the default frame this query used is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, a phrase we can now unpack piece by piece.

The Anatomy of a Frame Clause

A frame clause has two required halves: a mode (ROWS, RANGE, or GROUPS) and a pair of bounds that mark where the frame starts and ends relative to the current row. It can also take an optional third part, an EXCLUDE clause that removes specific rows from the frame the bounds selected; we come back to that later. The bounds are drawn from five keywords:

You combine two of these with BETWEEN ... AND ... to describe the window: the first is the start bound, the second is the end bound. What a "unit" means (a physical row, a value distance, or a group of peers) is decided by the mode, which is the distinction the rest of this article is about.

ROWS: Counting Physical Rows

ROWS is the most literal mode: a "unit" is one physical row, counted by position in the ordering. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW means, quite simply, "the row right before this one, plus this one," two rows, regardless of what values they hold. This is the mode you want for most sliding-window calculations. Here we sum the current month with the one before it:

January has no preceding row, so its frame holds just itself: 1000.00. Every later month sums its own revenue with the previous month's: 1200 + 1200 = 2400 for March, 1500 + 1500 = 3000 for May, and so on. Notice that March and April are treated no differently for having equal-valued neighbours: ROWS counts positions, and is completely blind to whether values happen to tie.

The classic use: a moving average

Widen the frame to two rows back and you have a three-month moving average, the single most common reason to reach for an explicit frame:

From March onward the average is taken over a full three-month window: March averages Jan, Feb, Mar (1000 + 1200 + 1200 = 3400, ÷3 = 1133.33), and the window slides forward one month at a time. The first two rows have an incomplete window (January averages only itself, February averages itself and January), which is the expected, and usually desirable, behaviour at the leading edge of a series.

RANGE: Counting by Value, Not by Position

RANGE looks almost identical to ROWS in syntax but counts differently. It is value-based rather than position-based. With CURRENT ROW, that means the frame boundary is defined by the current row's peer group: all rows that tie on the ORDER BY value are pulled into the frame together, as if they were one indivisible point on the ordering. With a numeric or temporal offset (for example RANGE BETWEEN 100 PRECEDING AND CURRENT ROW), it instead means a value-based distance from the current row's ordering value: every row whose value falls within 100 of it.

The peer behaviour is invisible when the ORDER BY column is unique (which is why our running-total example earlier behaved normally). To expose it we need to order by a column with ties: revenue. But there is a subtlety worth being careful about, and it is exactly the kind of thing this article exists to teach. Under ROWS, ordering by revenue alone is not deterministic: the two 1200.00 rows are equal, so which one ROWS counts "first" is left to the engine. To make ROWS reproducible we must break the tie with a unique column:

ROWS extends the frame by exactly one position per row: id 2 reaches 2200, id 3 reaches 3400, and so on up the ordering. Now compare RANGE, ordered by revenue, and here we deliberately do not add a tiebreaker, because adding id would make each row its own peer group and destroy the very behaviour we want to show:

Look at the two 1200.00 rows. Under ROWS they got 2200 and 3400, each extended the frame by one position. Under RANGE they get the same 3400, because they are peers: RANGE includes every row whose revenue is 1200.00 or less, treating the two 1200.00 rows as a single point. The same happens to the 1500.00 pair, where both land on 6400. Rows without a tie (1000 and 1800) agree under both modes. This is the essential contrast: for a boundary of CURRENT ROW, ROWS stops at the physical row while RANGE stops at the end of its peer group.

GROUPS: Counting Groups of Peers

GROUPS is the newest and least widely supported of the three modes. Its unit is a group of peers: all the rows that tie on the ORDER BY value count as one group, and the frame bounds count groups rather than rows or values. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW therefore means "the current peer group, plus the one peer group before it."

The revenue values form four peer groups: {1000}, {1200, 1200}, {1500, 1500}, {1800}. Each row's frame is its own group plus the group immediately before it. The 1200 rows cover groups {1000} and {1200}: 1000 + 1200 + 1200 = 3400. The 1500 rows cover groups {1200} and {1500}: 1200 + 1200 + 1500 + 1500 = 5400. June, at 1800, covers groups {1500} and {1800}: 1500 + 1500 + 1800 = 4800. Where ROWS counts individual rows and RANGE counts value ranges, GROUPS lets you say "two peer groups back," useful when the distinct values, not the row count, are what you want to step through.

Excluding Rows from the Frame: EXCLUDE

The frame bounds decide which rows are eligible; an optional EXCLUDE clause can then remove specific rows around the current one from that eligible set, even though the bounds would otherwise include them. It comes in four forms:

The most immediately useful of the four is EXCLUDE CURRENT ROW. Pair it with a whole-partition frame and you get "the aggregate of every row except this one": the sum of all other rows, on each row:

The partition total is 8200.00. Each others_sum is that total minus the current row's own revenue: 8200 minus 1000 = 7200 for the first row, 8200 minus 1800 = 6400 for the last. Without EXCLUDE CURRENT ROW, every row would simply show 8200.00; the exclusion is what turns "the grand total" into "everyone but me," a common need for share-of-remainder and leave-one-out style calculations.

EXCLUDE GROUP and EXCLUDE TIES differ only when the ordering has peers, and the distinction is precise: GROUP removes the current row and its peers, while TIES removes the peers but keeps the current row. Over the whole partition ordered by revenue, the two 1200.00 rows (a peer group of two) show it clearly: EXCLUDE GROUP gives 8200 − 1200 − 1200 = 5800 (both peers gone), whereas EXCLUDE TIES gives 8200 − 1200 = 7000 (only the other peer gone, the current row stays). For a row with no peers, such as the lone 1000.00, GROUP behaves like EXCLUDE CURRENT ROW and TIES like EXCLUDE NO OTHERS.

The Frame Explains the LAST_VALUE() Surprise

Nothing demonstrates why the frame matters more sharply than LAST_VALUE(). Newcomers reasonably expect it to return the last value of the partition, the final month's revenue, on every row. It does not, and the reason is the default frame:

Every last_val is just the current row's own revenue, clearly not "the last value." The cause is now obvious: with ORDER BY present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which ends at the current row. Because month is unique in this dataset, the current row is also the last row in that default frame, so LAST_VALUE() returns the current row's revenue. (Had the ORDER BY column contained peers, the default RANGE frame would end at the last peer of the current row, still not the end of the partition.) To get the true final value of the partition, you must extend the frame's end bound all the way out:

With the frame explicitly extended to UNBOUNDED FOLLOWING, every row can now see all the way to the end of the partition, and LAST_VALUE() returns the genuine final revenue, 1800.00, on every row. The function was never broken; it was faithfully reporting the last value of a frame that happened to stop at the current row. This is the clearest possible argument for understanding frames rather than memorising function behaviour.

Practical Use Cases

Running total

The default frame with an ordered SUM() already gives a running total, as we saw. If you prefer to be explicit (and being explicit is a good habit with frames), spell it out with ROWS:

Moving average

A trailing average over a fixed number of rows: the trend-smoothing workhorse of any time series. N PRECEDING AND CURRENT ROW gives a trailing window of N + 1 rows:

Centred window

Frames can reach forward as well as back. A centred moving average (one row behind, the current row, and one row ahead) uses FOLLOWING on the end bound:

Share of a total

Combine a whole-partition frame with per-row arithmetic to express each row as a fraction of the grand total: the whole-partition sum sits on every row, ready to divide into:

Common Pitfalls

• Assuming ORDER BY alone gives a whole-partition aggregate. Adding ORDER BY silently switches the default frame to "start through current row," which turns a total into a running total. If you want the full-partition value, either omit ORDER BY or write an explicit UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame.
• Expecting LAST_VALUE() to return the partition's last row. By default the frame ends at the current row, so LAST_VALUE() returns the current row's value. Extend the end bound to UNBOUNDED FOLLOWING to get the true final value.
• Confusing ROWS and RANGE when the ordering column has ties. For the common UNBOUNDED PRECEDING ... CURRENT ROW frame, a unique ordering key means ROWS and RANGE produce the same set of rows, which lulls you into treating them as interchangeable. They are not: for offset frames like 2 PRECEDING AND CURRENT ROW, ROWS means "two rows back" while RANGE means "within a value distance of two," and the moment peers appear even the simple frame diverges: RANGE lumps tied rows together while ROWS keeps them separate.
• Using a frame with a function that ignores it. Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and offset functions (LAG, LEAD) are not frame-sensitive. Adding a frame clause to them has no effect, and expecting one leads to confusion.
• Reaching for GROUPS or EXCLUDE on an engine that lacks them. Both GROUPS and the EXCLUDE clause are supported by PostgreSQL, SQLite, and Oracle, but not by MySQL or SQL Server. Confirm your engine supports them before relying on them; for GROUPS a DENSE_RANK()-based rewrite works, and for EXCLUDE CURRENT ROW you can subtract the current row's value from a full-partition aggregate.

Cross-Database Support

Frame clauses are part of standard SQL, but coverage varies by mode and by engine. The table below summarises the situation for the major engines; compatibility details here refer to the current documentation available for each engine at the time of writing.

Two engine-specific traps are worth calling out because they are common sources of errors. First, SQL Server supports RANGE but not numeric RANGE offsets: a clause like RANGE BETWEEN 5 PRECEDING AND CURRENT ROW works in PostgreSQL, but SQL Server rejects it; there, RANGE is limited to the UNBOUNDED and CURRENT ROW forms, and you must use ROWS for a numeric offset. Second, the default-frame rule is not universal: PostgreSQL, MySQL, SQL Server, SQLite, and Oracle all use RANGE UNBOUNDED PRECEDING AND CURRENT ROW when an ORDER BY is present, but Amazon Redshift requires an explicit frame for an aggregate window function used with ORDER BY. In other words, the bare SUM(revenue) OVER (ORDER BY month) that gives a running total elsewhere is not portable to Redshift as written; you must spell out the ROWS frame.

The Three Modes at a Glance

Everything above condenses into one comparison worth keeping close:

Taking Control of the Frame

The clearest way to hold the whole idea together is as three questions a window definition answers in sequence: PARTITION BY asks "which rows belong together?", ORDER BY asks "in what order?", and the frame asks "which of those ordered rows can this calculation see right now?". The frame is the quiet third question, and once you can see it, a whole class of "why is this query doing that?" puzzles dissolves. The essentials fit in a few lines:

• Without ORDER BY, the default frame is the whole partition; with it, the frame runs from the start through the current row.
• ROWS counts physical rows: the right choice for moving averages and most sliding windows.
• RANGE counts by ORDER BY value, pulling tied peers into the frame together.
• GROUPS counts peer groups, stepping through distinct values, where supported.
• EXCLUDE removes the current row, its peer group, or its ties from whatever frame the bounds selected, most usefully EXCLUDE CURRENT ROW for "everyone but me."
• When a frame-sensitive function surprises you, LAST_VALUE() above all, the frame is almost always the explanation.

The practical habit that follows is simple: whenever you use a frame-sensitive function with ORDER BY, decide consciously what frame you want, and write it out. An explicit frame is a few extra words, and it removes all doubt about which rows your calculation can see.