SQL EXCLUDE

What is the SQL EXCLUDE clause?

EXCLUDE refines a window frame by omitting the current row, its peer group, tied rows, or all others from a window-function calculation.

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 EXCLUDE: Supported: PostgreSQL 14+, DuckDB, Trino/Presto, Oracle 21c, BigQuery. Not supported: MySQL, MariaDB, SQL Server (as of 2024), SQLite.

SQL EXCLUDE Full Explanation

EXCLUDE is part of the window frame clause that controls which of the rows inside an already defined frame are visible to the window function at each step. After the frame is built with PARTITION BY, ORDER BY, and a frame boundary (ROWS, RANGE, or GROUPS), the optional EXCLUDE modifier removes specific rows before the function is evaluated:- EXCLUDE CURRENT ROW – drops the row that is producing the result.- EXCLUDE GROUP – drops all rows that are peers of the current row (same ordering keys).- EXCLUDE TIES – drops peers that tie with the current row on the ORDER BY keys but keeps the current row itself.- EXCLUDE NO OTHERS – default; keeps every row in the frame.Because EXCLUDE is evaluated after the frame boundaries are resolved, it never changes frame start or end points; it only masks rows for the function call. Aggregate and ranking window functions, as well as user-defined window functions, honor the exclusion set. Implementations that support EXCLUDE usually also support GROUPS frames, as both were standardized together in SQL:2011.Caveats:1. Not all databases implement EXCLUDE even if they support window functions.2. Some engines restrict EXCLUDE to GROUPS frames or prohibit it with RANGE frames lacking ORDER BY.3. Performance can degrade on large peer groups because engines must determine ties per row.

SQL EXCLUDE Syntax

<window frame clause>
{ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end
[EXCLUDE { CURRENT ROW | GROUP | TIES | NO OTHERS }]

SQL EXCLUDE Parameters

CURRENT ROW (keyword) - excludes only the current row.
GROUP (keyword) - excludes all rows that share the ORDER BY values with the current row.
TIES (keyword) - excludes tied rows except the current row.
NO OTHERS (keyword) - default; retains all rows.
frame_start/frame_end - expressions defining the frame boundaries (not unique to EXCLUDE).

Example Queries Using SQL EXCLUDE

--Running total excluding the current row’s sales
SELECT id,
       sale_date,
       amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           EXCLUDE CURRENT ROW
       ) AS total_prior_sales
FROM sales;

--Average grade excluding ties with the top student in each class
SELECT student_id,
       class_id,
       grade,
       AVG(grade) OVER (
           PARTITION BY class_id
           ORDER BY grade DESC
           GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           EXCLUDE TIES
       ) AS avg_without_top_ties
FROM grades;

Expected Output Using SQL EXCLUDE

In the first query, total_prior_sales is the cumulative sales up to but not including the row’s own amount
In the second query, AVG() ignores rows whose grade ties with the current highest grade in the frame, giving a running average without the tied top scores

Use Cases with SQL EXCLUDE

Computing running totals that should not double-count the current row.
Producing cumulative averages that skip the current peer group to avoid self-bias.
Ranking scenarios where you need metrics for preceding rows only.
Financial calculations where ties or the current transaction must be omitted from aggregates.

Common Mistakes with SQL EXCLUDE

Using EXCLUDE in a database version that does not support it.
Expecting EXCLUDE to shrink the frame boundaries; it only filters rows after framing.
Confusing EXCLUDE GROUP with EXCLUDE TIES; GROUP removes the current row too.
Omitting ORDER BY and then using EXCLUDE GROUP/TIES, which rely on peer detection.

First Introduced In

SQL:2011 standard; first implemented in PostgreSQL 14

Frequently Asked Questions

What does EXCLUDE CURRENT ROW do?

It excludes the row currently being processed from the window frame, useful for prior running totals or averages.

Can I use EXCLUDE in MySQL or SQL Server?

No. Neither MySQL nor SQL Server support EXCLUDE as of 2024, even though they support other window function features.

Does EXCLUDE shrink the frame boundaries?

No. It filters rows after the frame is established; the start and end points remain unchanged.

How does EXCLUDE GROUP differ from EXCLUDE TIES?

GROUP removes all peer rows including the current one. TIES removes only peers with equal ORDER BY values, keeping the current row in the frame.