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.

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.

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.