WINDOW FRAME (ROWS/RANGE)Query Techniques
Buy Me A Coffee

Control which rows are included in a window function's calculation relative to the current row. The frame clause (ROWS or RANGE BETWEEN ... AND ...) determines the sliding window boundaries.

Defines the set of rows relative to the current row in a window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, for example. Required for FIRST_VALUE, LAST_VALUE, and NTH_VALUE to return meaningful results. Default varies by function.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 ROWS and RANGE frames supported since 8.0. GROUPS frame mode added in 8.0. Frame boundaries: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. RANGE with numeric/interval offsets supported.
PostgreSQL ✓ Supported 8.4 ROWS and RANGE supported since 8.4. GROUPS mode added in PG 11. RANGE with numeric/interval offsets. EXCLUDE clause (EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS) added in PG 11 for fine-grained control.
SQL Server ✓ Supported 2012 ROWS and RANGE supported since SQL Server 2012. No GROUPS mode. RANGE with numeric offsets supported but RANGE INTERVAL is not (use ROWS for date rolling windows instead). No EXCLUDE clause.
Oracle ✓ Supported 8i ROWS and RANGE supported since 8i. No GROUPS mode. RANGE with numeric or interval offsets. No EXCLUDE clause. Default frame when ORDER BY is present: RANGE UNBOUNDED PRECEDING TO CURRENT ROW.
SQLite ✓ Supported 3.25.0 ROWS and RANGE supported since 3.25.0. GROUPS mode added in 3.28.0. No EXCLUDE clause. RANGE with numeric offsets supported. No INTERVAL type, so RANGE date rolling requires storing dates as numeric types (Julian day or Unix epoch).

Details

Default frame when ORDER BY is present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — this is why LAST_VALUE usually returns the current row (it's the last in the default frame). ROWS mode is offset by physical row count; RANGE mode is offset by value distance (requires ORDER BY on a numeric/date). SQL Server does not support RANGE INTERVAL — use ROWS for rolling date windows instead. PostgreSQL 11's EXCLUDE clause is unique and useful for peer-comparison calculations.

Standard Syntax

SELECT order_date, amount, -- 7-day rolling sum (preceding rows by date range): SUM(amount) OVER ( ORDER BY order_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW ) AS rolling_7day FROM orders;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2012 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

ROWS and RANGE frames supported since 8.0. GROUPS frame mode added in 8.0. Frame boundaries: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. RANGE with numeric/interval offsets supported.

-- Running total: SELECT dt, amt, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM sales; -- 3-row moving average: SELECT dt, amt, AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3 FROM sales; -- 7-day rolling sum: SELECT dt, amt, SUM(amt) OVER ( ORDER BY dt RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW ) AS rolling_7day FROM sales;

PostgreSQL

ROWS and RANGE supported since 8.4. GROUPS mode added in PG 11. RANGE with numeric/interval offsets. EXCLUDE clause (EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS) added in PG 11 for fine-grained control.

-- Running total: SELECT dt, amt, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM sales; -- 3-row moving average: SELECT dt, amt, AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3 FROM sales; -- 7-day rolling (interval RANGE): SELECT dt, amt, SUM(amt) OVER ( ORDER BY dt RANGE BETWEEN '6 days'::interval PRECEDING AND CURRENT ROW ) AS rolling_7day FROM sales; -- EXCLUDE CURRENT ROW (PG 11+): SELECT dt, amt, AVG(amt) OVER ( ORDER BY dt ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW ) AS peer_avg FROM sales;

SQL Server

ROWS and RANGE supported since SQL Server 2012. No GROUPS mode. RANGE with numeric offsets supported but RANGE INTERVAL is not (use ROWS for date rolling windows instead). No EXCLUDE clause.

-- Running total: SELECT dt, amt, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM sales; -- 3-row moving average: SELECT dt, amt, AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3 FROM sales; -- Rolling 7-day (SQL Server: use ROWS, not RANGE INTERVAL): SELECT dt, amt, SUM(amt) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7row FROM sales;

Oracle

ROWS and RANGE supported since 8i. No GROUPS mode. RANGE with numeric or interval offsets. No EXCLUDE clause. Default frame when ORDER BY is present: RANGE UNBOUNDED PRECEDING TO CURRENT ROW.

-- Running total: SELECT dt, amt, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM sales; -- 3-row moving average: SELECT dt, amt, AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales; -- 7-day rolling (interval RANGE): SELECT dt, amt, SUM(amt) OVER ( ORDER BY dt RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW ) AS rolling_7day FROM sales;

SQLite

ROWS and RANGE supported since 3.25.0. GROUPS mode added in 3.28.0. No EXCLUDE clause. RANGE with numeric offsets supported. No INTERVAL type, so RANGE date rolling requires storing dates as numeric types (Julian day or Unix epoch).

-- Running total: SELECT dt, amt, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM sales; -- 3-row moving average: SELECT dt, amt, AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales;