SQL PRECEDING

What does the SQL PRECEDING keyword do?

PRECEDING defines the upper boundary of a window frame relative to the current row.

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 PRECEDING: Supported in PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.28+, Snowflake, BigQuery, Redshift, and most ANSI-compliant engines.

SQL PRECEDING Full Explanation

PRECEDING is a window-frame boundary keyword that works with window functions. It specifies how many rows (or range of values) before the current row should be included when the frame is evaluated. PRECEDING can be paired with a numeric literal or with the modifier UNBOUNDED. The phrase appears only inside the frame clause that follows OVER. Frames are evaluated after the rows in the partition are ordered, so missing ORDER BY causes an error in vendors that require it. PRECEDING can be used with ROWS, RANGE, or GROUPS frame units, but availability varies by dialect. If you omit a lower boundary, the default is BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SQL PRECEDING Syntax

-- basic form
<window_function>() OVER (
  PARTITION BY <expr_list>
  ORDER BY <sort_expr>
  ROWS BETWEEN <n> PRECEDING AND <m> FOLLOWING
);

-- unbounded form
<window_function>() OVER (
  ORDER BY <sort_expr>
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

SQL PRECEDING Parameters

• n (integer) - positive number of rows or value offsets before the current row.
• UNBOUNDED - keyword indicating the first row in the partition.
• m (integer/FOLLOWING/CURRENT ROW) - closing boundary paired with PRECEDING.
• Frame unit (ROWS, RANGE, GROUPS) - determines how offsets are interpreted.

Example Queries Using SQL PRECEDING

-- Running 7-day moving average of sales
SELECT order_date,
       amount,
       AVG(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7d
FROM   sales;

-- Cumulative sum within each customer
SELECT customer_id,
       order_id,
       amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM   orders;

Expected Output Using SQL PRECEDING

Each row gains a new column containing the aggregated value calculated over the specified frame
For the moving-average query, the first six rows use a shorter frame until seven rows are available; thereafter every frame contains the current row plus the six preceding rows

Use Cases with SQL PRECEDING

Moving averages, running totals, and other sliding window analytics
Percentile or rank calculations that need look-back periods
Financial and time-series reporting where prior rows must be included but future rows excluded

Common Mistakes with SQL PRECEDING

Omitting ORDER BY in the window, leading to nondeterministic results or errors
Using a negative offset (not allowed)
Mixing ROWS with non-numeric PRECEDING offsets
Expecting PRECEDING to work outside a window clause

First Introduced In

SQL:2003 (window functions)

Frequently Asked Questions

What happens if I omit the lower boundary?

If you provide only an upper boundary, the frame defaults to BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Does PRECEDING work with RANGE frames?

Yes, but RANGE interprets the offset based on the ORDER BY expression’s value rather than row count. Only numeric or interval expressions are allowed.

Can PRECEDING reference future rows?

No. PRECEDING always looks backward. To look forward, use FOLLOWING.

How do I include the entire partition?

Use UNBOUNDED PRECEDING as the lower boundary and CURRENT ROW or UNBOUNDED FOLLOWING as the upper boundary depending on the desired span.