---
schema_version: "1"
id: 9ae96593664e5edaf188c6e37f36bf790401f6baf3e7f06e8475ba56c5f1d4c9
title: WINDOW Clause
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/sql-commands/select-window.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-window.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: cd695b790aba2087c7bab57adaa5eeea7a0b650792526ce2a52c8a4565fa3b60
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# `WINDOW` Clause

The `WINDOW` clause returns a partition of the data returned by the select command. Window functions reference the `WINDOW` clause in their `OVER` clauses. For more information about window functions, see [Window Functions and Queries](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/window.md).

To copy an existing `WINDOW`, provide an `<existing_window_name>` that is an earlier entry in the `WINDOW` list. The new window copies its partitioning and ordering clauses from the existing window. The new window can’t specify its own `PARTITION BY` clause, but it can specify `ORDER BY` if the existing window doesn’t have one. The new window uses the copied frame clause.

The parts of the `PARTITION BY` list are similar to parts of a [GROUP BY Clause](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-group-by.md), but they’re simple expressions and aren’t an output column. The `PARTITION BY` can contain aggregate function calls.

The parts of the `ORDER BY` list are similar to parts of an [ORDER BY Clause](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-order.md), but they’re simple expressions and aren’t an output column.

## Framing

The optional `<frame_clause>` is a set of related rows for each row of the query.

If you define the `<frame_start>` but not the `<frame_end>`, `<frame_end>` defaults to `CURRENT ROW`. The `<frame_start>` can’t be `UNBOUNDED FOLLOWING` and `<frame_end>` can’t be `UNBOUNDED PRECEDING`.

The default framing option is `RANGE UNBOUNDED PRECEDING`, which sets the frame to be all rows from the partition start through the current row’s last peer. The row’s last peer is a row that the window’s `ORDER BY` clause considers the current row. If there’s no `ORDER BY` clause, all rows are peers. Generally, `UNBOUNDED PRECEDING` means that the frame starts with the first row of the partition, and `UNBOUNDED FOLLOWING` means the frame ends with the last row of the partition. `UNBOUNDED PRECEDING` and `UNBOUNDED FOLLOWING` ignore the defined `RANGE` or `ROWS` mode.

In `ROWS` mode, `CURRENT ROW` means that the frame starts or ends with the current rows. In `RANGE` mode, `CURRENT ROW` means that the frame starts or ends with the current row’s first or last peer in the `ORDER BY` ordering.

The `<offset> PRECEDING` and `<offset> FOLLOWING` definitions depend on the frame mode.

- In `ROWS` mode, the `<offset>` is an integer indicating that the frame starts or ends that many rows before or after the current row.
- In `RANGE` mode, the `<offset>` option requires one `ORDER BY` column in the window definition. If `PRECEDING` is set, the frame contains the rows that the ordering column value is less than `<offset>`. If `FOLLOWING` is set, the current row’s ordering column value is more than `<offset>`.

The data type of the `<offset>` expression depends on the data type of the ordering column. For datetime ordering columns, the offset is an `interval`. The value of the `<offset>` is non-null and non-negative. The `<offset>` can’t contain variables, aggregate functions, or window functions.

The `<frame_exclusion>` option excludes rows around the current row from the frame, even if they’re included in the frame start and frame end options.

- `EXCLUDE CURRENT ROW` excludes the current row from the frame.
- `EXCLUDE GROUP` excludes the current row and its ordering peers from the frame.
- `EXCLUDE TIES` excludes any peers of the current row from the frame, but not the current row itself.
- `EXCLUDE NO OTHERS` doesn’t exclude the current row or its peers.

If rows aren’t ordered uniquely in the `ORDER BY` ordering, `ROWS` mode can produce unpredictable‌ results. The `RANGE` mode ensures that rows that are peers in the `ORDER BY` ordering are in the frame or excluded from it.

## Syntax

```sql
SELECT [ALL | DISTINCT [ON (<expression>[, ...])]]
    ...
    [WINDOW <window_name> AS (<window_definition>)[, ...]]
    ...
```

The optional `WINDOW` is written as:

```sql
WINDOW <window_name> AS (<window_definition>)[, ...]
```

`<window_name>` is a name that can be referenced from `OVER` clauses or subsequent window definitions, and the `<window_definition>` is defined as:

```sql
[<existing_window_name>]
[PARTITION BY <expression>[, ...]]
[ORDER BY <expression> [ASC | DESC] [NULLS {FIRST | LAST}][, ...]]
[<frame_clause>]
```

This example shows the possible `<frame_clause>` syntax.

```sql
{RANGE | ROWS} <frame_start> [<frame_exclusion>]
{RANGE | ROWS} BETWEEN <frame_start> AND <frame_end> [<frame_exclusion>]
```

With `<frame_start>` and `<frame_end>` defined in this sample.

```sql
UNBOUNDED PRECEDING
<offset> PRECEDING
CURRENT ROW
<offset> FOLLOWING
UNBOUNDED FOLLOWING
```

The `<frame_exclusion>` syntax is provided in this sample.

```sql
EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS
```