SQL UNBOUNDED

What is the SQL UNBOUNDED keyword?

UNBOUNDED marks an open-ended limit in a window frame, meaning the frame extends to the first or last row of the partition.

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 UNBOUNDED: PostgreSQL, MySQL 8+, SQL Server, Oracle, Snowflake, BigQuery, Redshift, SQLite 3.25+

SQL UNBOUNDED Full Explanation

UNBOUNDED is used only inside window frame clauses (ROWS or RANGE) as part of the phrases UNBOUNDED PRECEDING or UNBOUNDED FOLLOWING. It signals that the frame starts with the first row in the partition (UNBOUNDED PRECEDING) or ends with the last row in the partition (UNBOUNDED FOLLOWING). When paired with BETWEEN, it creates running or cumulative calculations. If no BETWEEN is specified, UNBOUNDED PRECEDING is implied as the default frame start when ORDER BY is present. UNBOUNDED never appears by itself; it must be combined with PRECEDING or FOLLOWING. Attempting to use it outside a window definition raises a syntax error. Some dialects allow UNBOUNDED with RANGE but not ROWS for specific data types, so consult vendor docs.

SQL UNBOUNDED Syntax

<window_function>() OVER (
  PARTITION BY <expr>
  ORDER BY <expr>
  ROWS|RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

SQL UNBOUNDED Parameters

Example Queries Using SQL UNBOUNDED

-- Running total of sales per customer
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
      PARTITION BY customer_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sales
FROM orders;

-- Percent rank across entire partition
SELECT
  employee_id,
  salary,
  PERCENT_RANK() OVER (
      ORDER BY salary
      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS pct_rank
FROM employees;

-- Last value in the partition regardless of current row
SELECT
  product_id,
  sales_month,
  last_value(sales_month) OVER (
      PARTITION BY product_id
      ORDER BY sales_month
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS final_month
FROM product_sales;

Expected Output Using SQL UNBOUNDED

Each query returns the same row count as its input but adds a calculated column whose frame stretches to the first or last row of the partition, enabling running totals, full-set ranks, or look-ahead values

Use Cases with SQL UNBOUNDED

Compute running totals or moving aggregates that start at the first row
Calculate rankings or percentages over an entire partition
Fetch last or first values without self-joins
Build cumulative metrics in reporting queries

Common Mistakes with SQL UNBOUNDED

Using UNBOUNDED without PRECEDING or FOLLOWING
Forgetting ORDER BY, which can change default frame behavior
Mixing ROWS and RANGE where the dialect supports only one with UNBOUNDED
Expecting UNBOUNDED to work outside window functions

First Introduced In

SQL:2003

Frequently Asked Questions

What is the difference between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING?

UNBOUNDED PRECEDING sets the frame start at the first row of the partition. UNBOUNDED FOLLOWING sets the frame end at the last row.

Do I need BETWEEN when I use UNBOUNDED?

Not always. If BETWEEN is omitted, most databases assume "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" when ORDER BY is present.

Does UNBOUNDED impact performance?

Frames that include many rows can increase memory usage, but most engines optimize running-total calculations. Indexing the ORDER BY columns helps.

Why does my query error with UNBOUNDED in SQLite?

SQLite supports UNBOUNDED only from version 3.25 onward and only with ROWS frames. Ensure your version and syntax are correct.