> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Window functions

> Compute over a set of rows related to the current one, without collapsing them.

**Applies to:** Embedded ❌, Enterprise ✅

A window function computes across a set of rows related to the current one and
returns a value **per row**. That is the difference from
[aggregation](https://docs.lancedb.com/sql/dql/aggregation.md), which collapses the rows it covers.

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT
    title,
    score,
    row_number() OVER (PARTITION BY category ORDER BY score DESC) AS rank
FROM documents
```

Every row survives; each one gains its rank within its category.

## The window

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
OVER (
    [PARTITION BY expr [, ...]]
    [ORDER BY expr [, ...]]
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
)
```

`PARTITION BY` splits the rows into independent groups. `ORDER BY` orders them
inside each partition. Supply an ordering for useful ranks, and a stable
tie-breaker when selecting one row from several equal values. The frame clause narrows the window further, which is
how running totals and moving averages are expressed:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT
    created_at,
    sum(amount) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING) AS running_total,
    avg(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS trailing_7
FROM transactions
```

A `WINDOW` clause names a definition when several functions share one:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT
    row_number() OVER w,
    lag(score) OVER w
FROM documents
WINDOW w AS (PARTITION BY category ORDER BY score DESC)
```

## The functions

Registered in DataFusion 54.

**Ranking**

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
row_number  rank  dense_rank  percent_rank  cume_dist  ntile
```

**Offset and positional**

```text theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
lag  lead  first_value  last_value  nth_value
```

**Aggregates**

Any [aggregate function](https://docs.lancedb.com/sql/dql/aggregation.md#functions) used with `OVER` becomes a
window function. This is how running totals and moving averages are written.

## Deduplicating with a window

The common practical use: keep one row per key, chosen by some ordering. A
window ranks, and an outer query filters:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
WITH ranked AS (
    SELECT *, row_number() OVER (PARTITION BY doc_id ORDER BY version DESC) AS rn
    FROM revisions
)
SELECT * FROM ranked WHERE rn = 1
```

The outer query is needed because a window function cannot appear in `WHERE`:
windows are computed after filtering, so the predicate would have nothing to
read.

> [!NOTE]
>
> Window functions sort within each partition, so on a large table they are among
> the more expensive things you can ask for. Filter first, and check the cost with
> [Explain Analyze](https://docs.lancedb.com/basics/sql.md#analyze-a-query-plan).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.