> ## Documentation Index
> Fetch the complete documentation index at: https://risingwavelabs-wyx-add-timestamp-dedicated-page.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Window functions

> Window functions compute a single result for each row over a set of rows that are related to the current row (the "window").

For details about the syntax of window function calls, see [Window function calls](/sql/query-syntax/value-exp#window-function-calls).

## General-purpose window functions

### `row_number()`

The `row_number()` function assigns a unique sequential integer to each row within a partition of a result set. The numbering starts at 1 for the first row in each partition and increments by 1 for each subsequent row.

`row_number()` can be used to turn non-unique rows into unique rows. This could be used to eliminate duplicate rows.

The syntax of `row_number()` is:

```sql theme={null}
row_number() → integer
```

```sql Example theme={null}
SELECT
    row_number() OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as r,
    col1, col2
FROM t ORDER BY col1, col2;
----
 r | col1 | col2
---+------+------
 1 |    a |  100
 2 |    a |  105
 1 |    b |  103
 2 |    b |  107
 3 |    b |  107
 4 |    b |  109
```

<Note>
  We recommend using `row_number()` only for top-N pattern queries. For details about this pattern, see [Top-N by group](/processing/sql/top-n-by-group).
</Note>

### `rank()`

`rank()` returns the rank of the current row, with gaps; that is, the `row_number` of the first row in its peer group.

The syntax of `rank()` is:

```sql theme={null}
rank() → integer
```

```sql Example theme={null}
SELECT
    rank() OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as r,
    col1, col2
FROM t ORDER BY col1, col2;
----
 r | col1 | col2
---+------+------
 1 |    a |  100
 2 |    a |  105
 1 |    b |  103
 2 |    b |  107
 2 |    b |  107
 4 |    b |  109
```

### `dense_rank()`

`dense_rank()` returns the rank of the current row, without gaps; that is, if some rows share the same rank, the row next to them is assigned the next consecutive rank.

The syntax of `dense_rank()` is:

```sql theme={null}
dense_rank() → integer
```

```sql Example theme={null}
SELECT
    dense_rank() OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as r,
    col1, col2
FROM t ORDER BY col1, col2;
----
 r | col1 | col2
---+------+------
 1 |    a |  100
 2 |    a |  105
 1 |    b |  103
 2 |    b |  107
 2 |    b |  107
 3 |    b |  109
```

### `lag()` and `lead()`

`lag()` allows you to access the value of a previous row in the result set. You can specify the number of rows to look back.

The syntax of `lag()` is:

```sql theme={null}
lag ( value anycompatible [, offset const integer] ) → anycompatible
```

```sql Example theme={null}
SELECT
    col1, col2,
    lag(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as lag
FROM t ORDER BY col1, col2;
----
 col1 | col2 |  lag
------+------|------
    a |  100 | NULL
    a |  105 |  100
    b |  103 | NULL
    b |  107 |  103
    b |  107 |  107
    b |  109 |  107
```

`lead()` is similar to `lag()`, but it allows you to access the value of a subsequent row in the result set.

The syntax of `lead()` is:

```sql theme={null}
lead ( value anycompatible [, offset const integer] ) → anycompatible
```

```sql Example theme={null}
SELECT
    col1, col2,
    lead(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as lead
FROM t ORDER BY col1, col2;
----
 col1 | col2 | lead
------+------|------
    a |  100 |  105
    a |  105 | NULL
    b |  103 |  107
    b |  107 |  107
    b |  107 |  109
    b |  109 | NULL
```

### `first_value()` and `last_value()`

The `first_value()` function returns the value of the first row in the current window frame. If `IGNORE NULLS` is present, `first_value()` returns the first non-null value.

The syntax of `first_value()` is:

```sql theme={null}
first_value ( value anyelement [ IGNORE NULLS ] ) → anyelement
```

```sql Example theme={null}
SELECT
    col1, col2,
    first_value(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
    ) as first
FROM t ORDER BY col1, col2;
----
 col1 | col2 | first
------+------|-------
    a |  100 |   100
    a |  105 |   100
    b |  103 |   103
    b |  107 |   103
    b |  107 |   103
    b |  109 |   103
```

`last_value()` returns the value of the last row in the current window frame. If `IGNORE NULLS` is present, `last_value()` returns the last non-null value.

The syntax of `last_value()` is:

```sql theme={null}
last_value ( value anyelement [ IGNORE NULLS ] ) → anyelement
```

```sql Example theme={null}
SELECT
    col1, col2,
    last_value(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
        -- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as last
FROM t ORDER BY col1, col2;
----
 col1 | col2 | last
------+------|------
    a |  100 |  100
    a |  105 |  105
    b |  103 |  103
    b |  107 |  107
    b |  107 |  107
    b |  109 |  109
```

```sql Example theme={null}
SELECT
    col1, col2,
    last_value(col2) OVER (
        PARTITION BY col1
        ORDER BY col2
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last
FROM t ORDER BY col1, col2;
----
 col1 | col2 | last
------+------|------
    a |  100 |  105
    a |  105 |  105
    b |  103 |  109
    b |  107 |  109
    b |  107 |  109
    b |  109 |  109
```

<Note>
  Added in v2.3: Support `IGNORE NULLS`.
</Note>

## Aggregate window functions

All aggregate functions, including builtin ones such as `sum()` and `min()`, user-defined ones and `AGGREGATE:`-prefixed scalar functions, can be used as window functions.

For the complete list of builtin aggregate functions and their usage, see [Aggregate functions](/sql/functions/aggregate).