Window frame specification (ROWS BETWEEN) in SQL

Introduction

Window frame specification lets you control which rows are included when calculating values over a group of rows in a table.

When you want to calculate running totals in a sales report.

When you need to find the average of the last 3 days' temperatures for each day.

When you want to compare each row's value with the previous two rows in a list.

When you want to calculate a moving average or sum over a specific range of rows.

Syntax

SQL

OVER (ORDER BY column_name ROWS BETWEEN frame_start AND frame_end)

The frame_start and frame_end define the range of rows to include relative to the current row.

You can use keywords like UNBOUNDED PRECEDING, CURRENT ROW, or a number of rows before or after.

Examples

Calculates a running total of sales from the first row up to the current row.

SQL

SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Calculates the average score for the current row and the two rows before it.

SQL

AVG(score) OVER (ORDER BY game_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Counts the number of rows from the current row to the next three rows.

SQL

COUNT(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)

Sample Program

This query calculates the sum of the current sale and the two previous sales ordered by date, showing a running sum over the last 3 rows.

SQL

CREATE TABLE sales_data (
  id INT,
  sale_date DATE,
  amount INT
);

INSERT INTO sales_data VALUES
(1, '2024-01-01', 100),
(2, '2024-01-02', 150),
(3, '2024-01-03', 200),
(4, '2024-01-04', 50),
(5, '2024-01-05', 300);

SELECT
  id,
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_sum_last_3
FROM sales_data
ORDER BY sale_date;

Important Notes

The frame moves with each row, so calculations update as you go down the table.

ROWS BETWEEN counts physical rows, not values, so duplicates are included as separate rows.

If you omit ROWS BETWEEN, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which behaves differently.

Summary

Window frame specification controls which rows are included in window function calculations.

ROWS BETWEEN lets you specify a range of rows relative to the current row.

It is useful for running totals, moving averages, and other calculations over a sliding window.