What is the difference between ROWS and RANGE in a window frame clause?
answer
- one mode ignores the values entirely
- think about what happens on duplicates
- peers are rows tied on the ordering key
- CURRENT ROW means two different things
- physical position versus ordering value
basics
~20 sROWS counts physical rows around the current row, so every row gets its own frame. RANGE counts ORDER BY values, so all rows tied on the ordering key share one frame and produce the same result.
solid answer
~40 sThey are two ways of measuring the distance from the current row. **ROWS** is positional: `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` means exactly the current row plus the two rows physically before it in the window ordering, whatever their values. **RANGE** is value-based: bounds are interpreted against the `ORDER BY` value, and rows sharing that value — *peers* — are never split. In RANGE mode `CURRENT ROW` means "through the last row with my ordering value", so all tied rows compute the same answer. On a strictly unique ordering key the two modes agree exactly; on repeatable keys such as a date column with many rows per day they diverge, and a running total written with the RANGE default will repeat the group's end value across every tied row.
go deeper
Know that a window frame exists and that ROWS counts rows while RANGE counts values; be able to read ROWS BETWEEN 2 PRECEDING AND CURRENT ROW out loud as a three-row window.
Explain peers, state that CURRENT ROW means the last peer in RANGE mode, and predict both output columns for a small table with a tied ordering key.
Diagnose a reported wrong running total by spotting the implicit RANGE frame over a repeatable key, and argue which mode the business question actually calls for.
Set the standard: whether analytical queries in the codebase must always state the frame, and how to keep reported figures stable when an ordering key that used to be unique stops being so.
## Where the frame sits A window specification is `OVER (PARTITION BY … ORDER BY … <frame>)`. `PARTITION BY` makes independent groups, `ORDER BY` orders the rows inside a group, and the frame picks which slice of that ordered partition the function reads *for the current row*. The frame is recomputed row by row. `ROWS`, `RANGE` and `GROUPS` are the frame **modes**: they answer the question "in what unit do I measure `2 PRECEDING`?" ## ROWS: count physical rows `ROWS` counts positions in the ordered partition. `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` is literally at most three rows, and `ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING` is at most two. The ordering values are irrelevant to the counting — they only decide the order. Two rows with the same `ORDER BY` value still occupy different positions, so they get different frames and generally different results. Because ROWS is positional, it is deterministic only to the extent that the ordering is. If the `ORDER BY` key has ties, which tied row lands first is unspecified, so a ROWS frame over a non-unique key can produce different (though equally legal) numbers on different runs. Adding a tiebreaker column to the window `ORDER BY` fixes that. ## RANGE: count ordering values `RANGE` interprets bounds against the *value* of the ordering key. The critical consequence is what `CURRENT ROW` means: in RANGE mode it is not "me", it is "the boundary of my peer group" — all rows whose ordering value equals mine. As a start bound it means the first such row; as an end bound, the last. RANGE therefore never cuts a peer group in half: every peer sees an identical frame and returns an identical value. The simple, universally supported RANGE frames are the ones built from `UNBOUNDED PRECEDING`, `CURRENT ROW` and `UNBOUNDED FOLLOWING`. RANGE additionally allows *offset* bounds (`RANGE BETWEEN 5 PRECEDING AND CURRENT ROW`, or an `INTERVAL` offset on a date column), which define a genuine value window — but those require exactly one ordering column of a type that supports the arithmetic, and engine support for them arrived later than basic window support. ## Worked example Take `sales(sale_date, amount)` with rows `(2024-01-01, 10)`, `(2024-01-02, 20)`, `(2024-01-02, 30)`, `(2024-01-03, 40)`: ```sql SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows, SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range FROM sales; ``` `by_rows` gives 10, 30, 60, 100 — one row at a time. `by_range` gives 10, 60, 60, 100 — the two rows dated 2024-01-02 are peers, so both show the day's closing total of 60. Neither column is wrong; they answer different questions. "How much had accumulated by the time this individual sale was recorded?" is ROWS. "How much had accumulated by the end of this sale's day?" is RANGE. ## Why this is asked so often Because the **default** frame, applied whenever a window has `ORDER BY` and no explicit frame, is the RANGE one. A developer writes `SUM(amount) OVER (ORDER BY sale_date)` expecting the ROWS behaviour and gets peer-inclusive numbers, then reports a "wrong running total". Once you know that `CURRENT ROW` means something different in each mode, the result stops being mysterious. ## Choosing between them - Use **ROWS** when you mean a count of rows: the last N rows, a moving window of fixed length, a per-row accumulation, anything where each row must have its own answer. ROWS is also the more widely and uniformly supported mode. - Use **RANGE** when tied rows must agree, or when the window is genuinely defined by values rather than positions — a cumulative figure per calendar day, or a span expressed in units of the ordering column. - Use **GROUPS** when the unit you want is "peer groups", for example "this tied group and the two tied groups before it", regardless of how many rows each contains. ## A practical rule Check whether the window `ORDER BY` key can repeat. If it cannot, the modes coincide and the default is harmless. If it can, pick the mode on purpose and write it out. Making the frame explicit costs one line and removes the single most common source of "the numbers look almost right" bugs in window queries.
- If the window ORDER BY key is unique for every row, do ROWS and RANGE produce the same result?Yes, for the simple `UNBOUNDED PRECEDING`/`CURRENT ROW` style frames. With a unique key each row is its own peer group, so "through my last peer" and "through me" select the same rows. The modes only diverge when the ordering key repeats — or when you use RANGE with an explicit offset, which is value arithmetic that ROWS cannot express at all.
- When would you deliberately choose RANGE over ROWS?When tied rows must show the same number — a daily cumulative figure where every transaction on a date should report that date's closing total — or when the window is defined in units of the ordering column rather than in rows, such as "all readings within 7 days before this one". ROWS cannot express either, because it has no access to the ordering values.
- Is a ROWS frame deterministic when the window ORDER BY has ties?Not fully. ROWS counts positions, and the order among tied rows is unspecified, so which rows land inside a `2 PRECEDING` window can vary between executions. The fix is to make the window ordering total by appending a unique tiebreaker column, for example `ORDER BY sale_date, sale_id`.
ROWS is a stopwatch that ticks once per person in the queue; RANGE is a clock that ticks once per minute, so everyone who arrived in the same minute is measured identically.
saying these in an interview costs you the question
- Claims ROWS and RANGE are interchangeable synonyms
- Thinks RANGE always means a numeric or date interval
- Says CURRENT ROW means the same thing in both modes
- Expects a running total to advance one row at a time regardless of mode
- Uses ROWS n PRECEDING expecting a time-based window