> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/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.
```sql Syntax 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
```
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).
### `rank()`
`rank()` returns the rank of the current row, with gaps; that is, the `row_number` of the first row in its peer group.
```sql Syntax 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.
```sql Syntax 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.
```sql Syntax 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.
```sql Syntax 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()`
Added in v2.3.0: Support `IGNORE NULLS`.
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.
```sql Syntax 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.
```sql Syntax 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
```
### Named window
Added in v2.5.0.
When multiple window functions share the same partitioning and ordering logic, repeating the same `OVER` clause can be error-prone. To avoid redundancy, you can define a **named window** in the `WINDOW` clause of a query and reference it by name in [window function calls](/sql/query-syntax/value-exp#window-function-calls).
```sql Syntax theme={null}
WINDOW window_name AS ...
```
```sql Example theme={null}
-- Without named window
SELECT
sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC),
avg(salary) OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
-- With named window
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
```
The `WINDOW` keyword is now a reserved keyword and cannot be directly used as table alias.
## 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).