Course contentsShow
← Data Science
Lesson 1015 of 2,14526. Advanced SQLFree lesson

ROWS vs RANGE Frame Specifications

Learn the difference between ROWS and RANGE clauses for defining window frame boundaries.

ROWS vs RANGE Frame Specifications

What you'll learn: How to precisely control which rows are included in window function calculations using two different frame boundary methods.

When working with window aggregation functions, you need to define a frame—the subset of rows within your partition that the function operates on. SQL offers two distinct approaches: ROWS and RANGE.

ROWS: Physical Row Counting

The ROWS clause defines your frame by counting actual rows in their physical order. It's literal and precise.

SUM(amount) OVER (ORDER BY date 
                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

This creates a rolling 3-row window: the current row plus the two rows immediately before it in the ordered set. If two rows have identical date values, ROWS still treats them as separate, distinct rows.

RANGE: Logical Value-Based Grouping

The RANGE clause defines your frame based on the logical value of the ordering column, not physical position.

SUM(amount) OVER (ORDER BY date 
                  RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

This includes all rows where the date value falls within 2 units before the current row's date. If five rows share the same date, RANGE treats them as peers—they're all included or excluded together.

The Key Difference

Think of ROWS as a sliding physical window over your data, while RANGE creates a logical boundary based on values. ROWS counts individual rows (1, 2, 3...), whereas RANGE considers value proximity.

When you have ties in your ORDER BY column, RANGE keeps tied rows together; ROWS may split them arbitrarily depending on their physical position.

Key Takeaway: Use ROWS for strict row-by-row window control; use RANGE when you need to group rows with equal or similar ordering values together in your frame.