SQL RANGE

What does the SQL RANGE keyword do?

RANGE defines a window frame whose start and end boundaries are determined by the logical value of the ORDER BY expression, not by physical row counts.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RANGE: PostgreSQL, MySQL 8.0+, SQL Server 2012+, Oracle 12c+, Snowflake, BigQuery, Redshift (partial), SQLite (3.25+)

SQL RANGE Full Explanation

RANGE is used inside the OVER() clause of a window function to specify how many rows are visible to that function when calculating its result. Unlike ROWS, which counts a fixed number of physical rows, RANGE groups together all rows that have ORDER BY values falling within the same logical interval around the current row. Standard SQL supports UNBOUNDED PRECEDING, CURRENT ROW, numeric or interval offsets, and UNBOUNDED FOLLOWING as frame boundaries. A RANGE frame is always inclusive and requires an ORDER BY clause that contains exactly one expression (or multiple expressions that together form a unique ordering in dialects that allow it). If the ORDER BY column is numeric, a numeric offset is allowed; if it is date or time based, an INTERVAL literal is required. RANGE is evaluated after PARTITION BY filtering but before the window function itself executes. It is case-insensitive, but best practice is to write it in upper case for readability. Some databases also use RANGE in table partitioning (PARTITION BY RANGE) or generated columns, but those contexts represent separate grammar rules.

SQL RANGE Syntax

<window_function>() OVER (
  PARTITION BY <expr_list>
  ORDER BY <expr>
  RANGE BETWEEN <frame_start> AND <frame_end>
);

-- common boundary keywords
<frame_start | frame_end> ::= UNBOUNDED PRECEDING
                           | n PRECEDING
                           | CURRENT ROW
                           | n FOLLOWING
                           | UNBOUNDED FOLLOWING

SQL RANGE Parameters

frame_start (boundary) - Marks the first row included in the frame
frame_end (boundary) - Marks the last row included in the frame
n (integer / interval) - Offset applied to the ORDER BY value (numerical or INTERVAL)

Example Queries Using SQL RANGE

-- 1. Running total of sales where rows share the same or earlier purchase_date
SELECT purchase_date,
       amount,
       SUM(amount) OVER (ORDER BY purchase_date
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM   orders;

-- 2. 7-day moving average of sign-ups
SELECT signup_date,
       COUNT(*) AS daily_signups,
       AVG(COUNT(*)) OVER (ORDER BY signup_date
                           RANGE BETWEEN INTERVAL '6 day' PRECEDING AND CURRENT ROW) AS moving_avg
FROM   users
GROUP  BY signup_date;

-- 3. Compare salary to peers within the same pay grade (same salary value)
SELECT employee_id,
       salary,
       AVG(salary) OVER (ORDER BY salary RANGE BETWEEN CURRENT ROW AND CURRENT ROW) AS peer_avg
FROM   employees;

Expected Output Using SQL RANGE

Each query returns every original row plus an additional column calculated with awareness of all rows whose ORDER BY values fall within the defined RANGE frame
Rows sharing identical ORDER BY values are always included together

Use Cases with SQL RANGE

Compute running totals or cumulative sums when multiple rows can share the same ordering value
Create moving averages based on time or numeric ranges rather than a fixed row count
Compare a row to all peers that have the same ORDER BY value (e.g., identical salary)
Financial and time-series analysis where gaps in dates require logical rather than physical frames

Common Mistakes with SQL RANGE

Expecting RANGE to count a fixed number of rows (use ROWS for that)
Omitting ORDER BY or using multiple ORDER BY columns in dialects that do not allow it
Using numeric offsets on date columns without an INTERVAL literal
Forgetting that rows with the same ORDER BY value are always included, causing larger frames than intended
Mixing RANGE with an unsorted result set, leading to nondeterministic output

First Introduced In

SQL:2003 window function specification

Frequently Asked Questions

What is the difference between RANGE and ROWS?

RANGE uses logical value equality on the ORDER BY expression, so all tied rows are processed together. ROWS counts a physical offset regardless of ties.

Can RANGE be used without ORDER BY?

No. RANGE requires an ORDER BY expression to measure logical distance between rows.

How do numeric vs. date columns affect RANGE offsets?

Numeric ORDER BY columns accept integer offsets (e.g., 3 PRECEDING). Date or timestamp columns must use INTERVAL literals (e.g., INTERVAL '7 day' PRECEDING).

Why does my RANGE frame include more rows than expected?

If multiple rows share the same ORDER BY value, RANGE includes every one of them, which can enlarge the frame beyond a simple row count.