SQL Window Frames: ROWS vs RANGE

When you use window functions, the most important (and most misunderstood) detail isn’t the function itself—it’s the window frame. Two queries can look identical, return the same number of rows, and still compute different answers just because they use ROWS versus RANGE.

In this guide, we’ll build the intuition for window frames, compare ROWS and RANGE, and walk through practical patterns you can reuse in real analytics work. By the end, you’ll be able to choose the right frame without guessing.

Ordered rows
   |
Define frame
  / \
ROWS (count rows)   RANGE (group equal ORDER BY values)
  \ / 
Compute window function

The Big Idea: A Window Frame Is the “Slice” You Calculate Over

Every window function has three jobs:

1. Partition your data (optional) with PARTITION BY.
2. Order rows inside each partition with ORDER BY.
3. Frame a subset of those ordered rows with ROWS or RANGE.

If you omit the frame, many databases assume a default like:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

That’s a range-based frame. It does not always mean “up to the current row.” Instead, it means “up to all rows that tie with the current row’s ORDER BY value.” This is the source of subtle bugs.

Quick Intuition

• ROWS counts physical rows in the ordered set. It always moves row-by-row.
• RANGE groups rows by equal ORDER BY values and moves value-by-value.

If your ordering column has duplicates, ROWS and RANGE can produce different results.

Interactive Example 1: Running Sum with Duplicates

Let’s create a small sales table where multiple transactions share the same date. Then we’ll compute a running sum using both frames.

What to notice:

• On 2026-02-01, RANGE includes both rows with that date at once, so running_range “jumps.”
• ROWS increases one row at a time, so the running sum grows more gradually.

If you intended to compute a transaction-level running total, ROWS is the correct choice. If you intended to compute a date-level running total (all sales on the same date together), RANGE is a better match.

When Should You Use RANGE?

RANGE is perfect when your “current row” should include all rows with the same ORDER BY value. Common use cases:

• Daily totals where many transactions share the same order_date.
• Price changes where multiple events have the same timestamp.
• Ranking and percentiles where ties should be treated as a single step.

But Be Careful: RANGE Isn’t Supported Everywhere

Some databases limit RANGE to numeric or date/timestamp columns. Others don’t support RANGE with INTERVAL at all. When in doubt, test your specific engine or use ROWS with a clear ordering column that’s unique.

Interactive Example 2: Moving Average for the Last 3 Rows

This is a classic rolling metric: the average of the current row plus the two previous rows. Here ROWS is the only sensible choice because we explicitly want three rows, not three values.

If you used RANGE here and two days shared the same value (e.g., two rows with the same day), the frame would expand to include all tied rows, which breaks the “last 3 rows” requirement.

Pattern Guide: Choosing the Right Frame

Use this checklist to pick a frame quickly:

• Use ROWS when:

  • You want a strict row count (last 3 rows, last 10 rows, etc.).
  • Your ordering column has duplicates but you still want row-by-row movement.
  • You’re doing row-level computations like running totals by transaction.

• Use RANGE when:

  • You want all rows that share the same ORDER BY value to be included together.
  • You’re computing cumulative metrics by time bucket (daily, weekly, monthly).
  • You want ties to move as a group.

A Common Pitfall: “Why Is My Running Total Jumping?”

If you see large jumps at duplicated ORDER BY values, you’re likely using RANGE unintentionally. Many SQL engines default to RANGE whenever you specify ORDER BY without a frame.

Fix it explicitly:

SUM(amount) OVER (
  ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

That single word—ROWS—can eliminate hours of confusion.

A Practical Decision Rule: Count Rows or Group Values?

When you are unsure which frame to choose, ask one question:

Should this calculation advance one physical row at a time, or should tied ORDER BY values advance together?

If the answer is "one row at a time," choose ROWS.

If the answer is "all rows with the same sort value should behave like one step," choose RANGE.

This sounds simple, but it maps directly to real analytics questions:

• transaction-by-transaction running balances -> ROWS
• daily cumulative totals where the day is the business grain -> RANGE
• moving average of the last 7 records -> ROWS
• cumulative metric by tied score or timestamp bucket -> RANGE

That business-grain framing is usually more useful than memorizing syntax.

Where Window Frame Bugs Show Up in Real Work

Window frame mistakes are especially common in reporting queries that look correct at first glance.

Finance or ledger-style running balances

If every transaction matters separately, ROWS is usually the safer choice. Otherwise, duplicate timestamps or posting dates can make balances jump in unexpected chunks.

Product analytics by event date

If the reporting grain is "all events on the same day together," RANGE can be exactly right because duplicates are meaningful and should move as one group.

Dashboard rolling averages

People often say "7-day average" when they actually mean "last 7 rows." Those are different requirements. If the dataset has missing dates, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is not the same as a true date-based seven-day frame.

That is why naming the business intent before writing the frame matters so much.

Stable Ordering Matters More Than Most People Realize

Even when ROWS is the correct choice, the output can still be confusing if the ordering is not deterministic.

For example:

ORDER BY sale_date

If many rows share the same date, the engine may still need a tie-breaker to produce a stable row sequence. In practice, this often means adding a second column such as id:

ORDER BY sale_date, id

This does not change the business meaning of the report. It makes the row-by-row behavior predictable.

A Debugging Workflow for Window Frame Mistakes

If a moving metric looks wrong, use this sequence:

1. Inspect the raw ordered rows first.
2. Check whether the ORDER BY column has duplicates.
3. State the intended business grain in words.
4. Decide whether that grain is row-based or value-based.
5. Declare the frame explicitly and compare the output row by row.

This is one of those topics where a 20-second verbal explanation often reveals the bug faster than staring at the SQL.

Best Practices for Window Frames

1. Always declare the frame explicitly for analytics queries. It makes intent clear and avoids engine defaults.
2. Make ordering columns deterministic. If two rows can share the same value, add a secondary tie-breaker (like an id) when you need stable ordering.
3. Align the frame with your business logic. “Last 7 days” is a time-based frame; “last 7 rows” is not.
4. Test on edge cases. Include duplicates and missing dates to ensure you get the expected behavior.
5. Comment your intent. Your future self (and your teammates) will thank you.

Related Articles

• Understanding SQL Window Functions: A Visual Guide for the broader mental model behind ranking, running totals, and partitions.
• SQL Conditional Aggregation: Beyond Basic GROUP BY for a complementary reporting pattern when you need one row per group instead of row-preserving analytics.
• Mastering CTEs: Writing Cleaner, Better SQL for structuring multi-step analytical queries so frame logic is easier to reason about.

Conclusion

ROWS and RANGE are subtle, but once you understand the difference, you’ll unlock far more precise analytics. Think of ROWS as a physical row counter and RANGE as a value-based grouping. If you want row-by-row movement, use ROWS. If you want ties to move together, use RANGE.

As a final check, read the output row-by-row and ask: Do I want to count rows or values? Answer that, and your frame choice becomes obvious.