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

# Window Aggregate Functions

Window functions operate on a set of rows and return a single value for each row from the underlying query. Like aggregate functions, window functions operate on a set of rows, but they do not reduce the number of rows returned by the query.

Unlike regular aggregate functions, window functions do not group rows into a single output row. Instead, the rows retain their separate identities, and the window function produces aggregate calculations that access more than just the current row in the query result.

The term window describes the set of rows on which the window function operates. A window function returns a value from the rows in a window.

The database uses the `ORDER BY` and `PARTITION BY` clauses to define the scope of the window.

**Syntax**

Window Aggregate functions follow this syntax with `window_function` representing any of the functions listed in [Supported Window Aggregate Functions](#supported-window-aggregate-functions).

```sql SQL theme={null}
window_function( arg1, arg2 [,...] [ IGNORE NULLS ] )
   OVER (
      [ PARTITION BY part_list ]
      [ ORDER BY order_list [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]
      [ <ROWS_or_RANGE_clause> ] )

<ROWS_or_RANGE_clause> ::=
{ ROWS | RANGE }
   { <window_frame_preceding>
   | BETWEEN { <window_frame_preceding> | <window_frame_following> }
   AND { <window_frame_preceding> | <window_frame_following> }
     }

<window_frame_preceding> ::=
{ UNBOUNDED PRECEDING | row_integer PRECEDING | CURRENT ROW }

<window_frame_following> ::=
{ UNBOUNDED FOLLOWING | row_integer FOLLOWING | CURRENT ROW }
```

| **Argument**        | **Data Type** | **Description**                                                                            |
| ------------------- | ------------- | ------------------------------------------------------------------------------------------ |
| `arg1, arg2 [,...]` | String        | The arguments of the window function. The arguments depend on the type of window function. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | String        | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | String        | Specifies the column by which the rows are ordered.                             |
| `row_integer` | Numeric       | An integer representing the number of rows to operate from the current row.     |

## **Window Aggregate Keywords**

Window aggregate functions use these clauses and keywords to designate the parameters for the query and output.

### PARTITION BY

This clause defines a set of rows within a result set. `PARTITION BY` operates similarly to a [GROUP BY](/general-sql-syntax#group-by) clause, except it does not reduce the number of rows in the result set.

### ORDER BY

Sorts the result set in ascending or descending order based on one or more specified columns.

An `ORDER BY` clause must be included on all window functions to ensure your query results are deterministic. Some window functions require an `ORDER BY` clause. See the syntax for each function for specifics. For details, see [ORDER BY](/general-sql-syntax#order-by).

### ASC or DESC

Specifies whether the values in the specified column should be sorted in `ASC` ascending or `DESC` descending order. `ASC` is the default sort order.

### NULLS FIRST, NULLS LAST, or IGNORE NULLS

Specifies if the database sorts NULL rows first `NULLS FIRST`, last `NULLS LAST`, or whether the database ignores NULLs `IGNORE NULLS`. The default is `NULLS LAST`.

### ROWS or RANGE

Limit the rows within the partition by specifying start and end points within the partition. Specify a range of rows with respect to the current row by logical association using `RANGE` or physical association using `ROWS`.

The `RANGE` clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. The system defines preceding or following rows based on the ordering in the `ORDER BY` clause. If you do not specify this clause, the default is ascending order.

The `ROWS` clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row.

Specifying a window frame by the `RANGE` keyword with the current row `CURRENT ROW` keyword includes all rows with the same values as the current row in the `ORDER BY` clause.

`ROWS` and `RANGE` clauses can include these options.

| **Keyword Clause**      | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `UNBOUNDED PRECEDING`   | Specifies that the window starts at the first row of the partition. This clause specifies a window starting point only.                                                                                                                                                                                                                                                                                                                                                                                                                   |
| `UNBOUNDED FOLLOWING`   | Specifies that the window ends at the last row of the partition. This clause specifies a window endpoint only. <br />For example: <br />`RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING` <br />This window frame starts with the current row and ends with the last row of the partition.                                                                                                                                                                                                                                              |
| `row_integer PRECEDING` | The `row_integer` value specifies the number of rows or values to precede the current row. <br />You cannot use the `PRECEDING` keyword in a `RANGE` clause.                                                                                                                                                                                                                                                                                                                                                                              |
| `row_integer FOLLOWING` | The `row_integer` value specifies the number of rows or values to follow the current row. <br />When you specify the `FOLLOWING` keyword as the window starting point, the endpoint must also use a `FOLLOWING` clause with a row value. <br />For example: <br />`ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING` <br />This window frame starts with the second row following the current row and ends with the tenth row following the current row. <br />You cannot use the `FOLLOWING` keyword in a `RANGE` clause.                       |
| `CURRENT ROW`           | When you specify the `CURRENT ROW` keyword in a `ROWS` clause, the window frame starts or ends at the current row. <br />When you specify this keyword in a `RANGE` clause, the window frame includes all rows with equal values. <br />For example:<br />`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` <br />This clause means that the window function operates on a frame of three rows in size, where the first two rows precede the current row (inclusive).<br />You can specify the current row as either a starting or an end point. |

## Supported Window Aggregate Functions

These functions are supported for window frames. Some examples use the `sys.dummy` virtual table to populate data. For details, see [Generate Tables Using sys.dummy](/generate-tables-using-sys-dummy).

<Info>
  You cannot use the `DISTINCT` keyword with window aggregation functions.
</Info>

### CUME\_DIST 

Returns a positive value less than one that represents a percentage of values that are less than or equal to the current value in the group. 

**Syntax**

```sql SQL theme={null}
CUME_DIST ()
   OVER ( [ PARTITION BY part_list ] ORDER BY order_list )
```

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example shows the cumulative distribution values for a table with generated numbers from one to 10.

```sql SQL theme={null}
SELECT x, CUME_DIST()
    OVER (ORDER BY x) AS d
    FROM (SELECT c1 AS x FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "x" | "d" |
|-----|-----|
|   1 | 0.1 |
|   2 | 0.2 |
|   3 | 0.3 |
|   4 | 0.4 |
|   5 | 0.5 |
|   6 | 0.6 |
|   7 | 0.7 |
|   8 | 0.8 |
|   9 | 0.9 |
|  10 | 1.0 |
```

### **DELTA**

Computes the finite difference between successive values of expression under the specified ordering. This is a backward difference, meaning the delta value for a specified row is the difference between the value of that row and the previous row. 

The function requires two rows of input data to calculate a first-degree difference, three rows of input data to calculate a second-degree difference, and so on.

However, rather than returning NULL for the first few rows, the function returns the correct values, such that using DELTA of degree `n` followed by DELTA of degree `-n` cancel each other out to return the initial result (other than floating point error accumulation). In general, using this window function over larger result sets with large degrees worsens floating-point error accumulation.

Integer degree values in the range `[-3, 3]` are generally stable up to approximately 5 million rows. Fractional degree values in the range `[-3, 3]` are generally stable up to approximately 1 million rows. The stable degree range is wider over smaller result sets and narrower over larger result sets. For example, fractional and integer degrees are stable over the range `[-4, 4]` up to approximately 80,000 rows.

<Warning>
  The function treats any NULL values in the input data as zero, which might cause unexpected results. Filter out NULL values before you use the window function.
</Warning>

**Syntax**

```sql SQL theme={null}
DELTA ( expression [, degree ])
   OVER (
      [ PARTITION BY part_list ]
      ORDER BY order_list )
```

| **Argument** | **Data Type** | **Description**                                                                                                                                                                                                                                                                                                                                                                                                             |
| ------------ | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `expression` | Numeric       | The column or expression to use for the DELTA calculation.                                                                                                                                                                                                                                                                                                                                                                  |
| `degree`     | Numeric       | Optional. <br />If `degree` is set to a positive integer greater than 1, the window function calculates the higher-order finite difference without using nested `DELTA` calls. <br />If negative, the calculation is an anti-difference (i.e., discrete integration). <br />You can specify fractional values, either positive or negative, and these values mean fractional differences or sums. <br />The default is `1`. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Examples**

**Calculate Difference Values Using** **Basic Numbers**

This example shows the delta values for a table with generated numbers from one to 10.

```sql SQL theme={null}
SELECT y, DELTA(y, 1)
    OVER (ORDER BY y) AS d
    FROM (SELECT DOUBLE(c1 * c1) AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "d" |
|-----|-----|
| 1   | 1   |
| 4   | 3   |
| 9   | 5   |
| 16  | 7   |
| 25  | 9   |
| 36  | 11  |
| 49  | 13  |
| 64  | 15  |
| 81  | 17  |
| 100 | 19  |
```

**Calculate Difference Values Using Partitioned Sales Data**

This example uses the `DELTA` function with a table of regional sales data to find the difference in sales by region and year.

```sql SQL theme={null}
SELECT
    region,
    year,
    sales_amount,
    DELTA(sales_amount) OVER (PARTITION BY region ORDER BY year) AS sales_delta
FROM
    regional_sales
ORDER BY
    region,
    year;
```

*Output*

```none Text theme={null}
| "region" | "year" | "sales_amount" | "sales_delta" |
|----------|--------|----------------|---------------|
| North    |   2018 |          12000 |         12000 |
| North    |   2019 |          15000 |          3000 |
| North    |   2020 |          17000 |          2000 |
| North    |   2021 |          16000 |         -1000 |
| North    |   2022 |          18000 |          2000 |
| South    |   2018 |          10000 |         10000 |
| South    |   2019 |          14000 |          4000 |
| South    |   2020 |          15000 |          1000 |
| South    |   2021 |          17000 |          2000 |
| South    |   2022 |          16000 |         -1000 |
```

### DENSE\_RANK

Assigns a rank to each row in the result set with equal values having the same number. There are no gaps between ranks. 

**Syntax**

```sql SQL theme={null}
DENSE_RANK ()
   OVER ( [ PARTITION BY part_list ] ORDER BY order_list )
```

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example generates the rank for a series of ten random numbers. Use the [RAND](/math-functions-and-operators#rand)  function to generate the random numbers.

```sql SQL theme={null}
WITH RandomNumbers AS (
    SELECT RAND() AS num
    FROM sys.dummy10
)
SELECT num, DENSE_RANK()
    OVER (ORDER BY num) AS n
    FROM RandomNumbers;
```

*Output*

```none Text theme={null}
|         num          |  n  |
| -------------------- | --- |
| 0.021744504292815926 | 1   |
| 0.11531389663905277  | 2   |
| 0.17818297230968436  | 3   |
| 0.3009914646699445   | 4   |
| 0.366713904443239    | 5   |
| 0.7801256114813641   | 6   |
| 0.8735727062571587   | 7   |
| 0.8969752860565523   | 8   |
| 0.9679736914744647   | 9   |
| 0.9910291449541664   | 10  |
```

### DERIVATIVE

The function calculates the difference between successive values of two numeric expressions, representing a backward difference (e.g., the difference between the current and previous row). It can be considered as computing the derivative of one expression with respect to another.

The function must use an `ORDER BY` clause, and it cannot use a `PARTITION BY` clause.

`DERIVATIVE` requires two arguments, `expression` and `expression2`, which must be included in the `ORDER BY` clause.

The function computes the difference in `expression` values between consecutive rows and divides this by the difference in `expression2` values over the same rows. If `expression` represents some measured value and `expression2` is time, the result is the rate of change (derivative) of the value with respect to time.

The function can compute higher-degree derivatives (second, third, etc.). To calculate the `nth`-degree derivative, it uses a backward difference formula that compares the current row to the previous `n` rows.

For example:

* A second-degree derivative requires three rows of data: the current row, the previous row, and the one before the previous row.
* A third-degree derivative would require four rows, and so on.

Unlike some functions that return NULL for rows that lack sufficient preceding data, the `DERIVATIVE` function is designed to return correct values even for initial rows. This behavior ensures that if you apply a derivative of degree `n` followed by its inverse (derivative of degree `-n`), you recover the original values with minimal error (except for floating point inaccuracies).

In general, using this function over larger result sets with larger degrees worsens the floating-point error. Integer degree values in the range of three are generally stable up to approximately 5 million rows. Fractional degree values in the range of three are generally stable up to approximately 1 million rows. The stable degree range is wider over smaller result sets and narrower over larger result sets. For example, fractional and integer degrees are stable over the range `[-4, 4]` up to approximately 80,000 rows. 

<Warning>
  For higher-order calls, the sample points for `expression` must be equally spaced, or the function might return incorrect results. In other words, the DELTA executions between consecutive `expression2` values must always be the same. If this is not true, use some form of interpolation to create an evenly-spaced series of samples before you use the window function.
</Warning>

<Warning>
  The function treats any NULL values in the input data for either `expression` or `expression2` arguments as zero, which might cause unexpected results. Filter out NULL values before you use the window function.
</Warning>

 **Syntax**

```sql SQL theme={null}
DERIVATIVE ( expression [, degree ] )
   OVER ( ORDER BY expression2 )
```

| **Argument**  | **Data Type** | **Description**                                                                                                                                                                                                                                                                                                                                                                |
| ------------- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `expression`  | Numeric       | The column or expression to use for the `DERIVATIVE` calculation compared to `expression2`.                                                                                                                                                                                                                                                                                    |
| `expression2` | Numeric       | The column or expression to use for the `DERIVATIVE` calculation compared to `expression`.                                                                                                                                                                                                                                                                                     |
| `degree`      | Numeric       | Optional. <br />If you set `degree` to a positive integer greater than one, the window function calculates the higher-order difference quotients (discrete derivatives) without using nested `DERIVATIVE` executions. <br />If the `degree` argument is a negative integer, the calculation is a discrete anti-differentiation or integration. <br />The default value is `1`. |

**Examples**

**Calculate the Derivative Using Basic Numbers**

This example shows the derivative values for a basic table with values `x` and `y`.

```sql SQL theme={null}
SELECT x, y, DERIVATIVE(y, 1)
    OVER (ORDER BY x) AS d
    FROM (SELECT DOUBLE(2 * x + 1) AS y, x
        FROM (SELECT c1/2.0 AS x FROM sys.dummy10));
```

*Output*

```none Text theme={null}
| "x" | "y"  | "d" |
|-----|------|-----|
| 0.5 |  2.0 | 4.0 |
| 1.0 |  3.0 | 2.0 |
| 1.5 |  4.0 | 2.0 |
| 2.0 |  5.0 | 2.0 |
| 2.5 |  6.0 | 2.0 |
| 3.0 |  7.0 | 2.0 |
| 3.5 |  8.0 | 2.0 |
| 4.0 |  9.0 | 2.0 |
| 4.5 | 10.0 | 2.0 |
| 5.0 | 11.0 | 2.0 |
```

**Calculate the Antiderivative Using a Negative Degree**

This example uses a negative degree to produce an antiderivative.

```sql SQL theme={null}
SELECT x, y, DERIVATIVE(y, -1)
    OVER (ORDER BY x) AS d
    FROM (SELECT DOUBLE(2 * x + 1) AS y, x
        FROM (SELECT c1/2.0 AS x FROM sys.dummy10));
```

*Output*

```none Text theme={null}
| "x" | "y"  | "d"  |
|-----|------|------|
| 0.5 |  2.0 |  1.0 |
| 1.0 |  3.0 |  2.5 |
| 1.5 |  4.0 |  4.5 |
| 2.0 |  5.0 |  7.0 |
| 2.5 |  6.0 | 10.0 |
| 3.0 |  7.0 | 13.5 |
| 3.5 |  8.0 | 17.5 |
| 4.0 |  9.0 | 22.0 |
| 4.5 | 10.0 | 27.0 |
| 5.0 | 11.0 | 32.5 |
```

### FIRST\_VALUE

Returns the first value in the ordered result set. 

**Syntax**

```sql SQL theme={null}
FIRST_VALUE ( expression )
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                          |
| ------------ | ------------- | ------------------------------------------------------------------------ |
| `expression` | Any           | The column or expression value to use for the `FIRST_VALUE` calculation. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example uses a table with generated numbers from one to 10. Of this group, the function determines the first value from the ordering.

```sql SQL theme={null}
SELECT y, FIRST_VALUE(y)
    OVER (ORDER BY y) AS first_value
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "first_value" |
|-----|---------------|
|   1 |             1 |
|   2 |             1 |
|   3 |             1 |
|   4 |             1 |
|   5 |             1 |
|   6 |             1 |
|   7 |             1 |
|   8 |             1 |
|   9 |             1 |
|  10 |             1 |
```

### LAG

Returns a row backward from the current row (one row back unless you specify a different number value).

<Info>
  For marking repeated LAG calls on one or more columns, see [Data Preparation](/data-preparation).
</Info>

**Syntax**

```sql SQL theme={null}
LAG (value_expr [, offset ])
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                                                                                   |
| ------------ | ------------- | --------------------------------------------------------------------------------------------------------------------------------- |
| `value_expr` | Any           | The column or expression to use for the `LAG` calculation.                                                                        |
| `offset`     | Numeric       | Optional. <br />This number determines how many rows back from the current row to return a value. <br />The default value is `1`. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Examples**

**Calculate Lag Using Basic Numbers**

This example uses a table with generated numbers from one to 10. Of this group, the `LAG` function returns the last value for the specified column `y`.

```sql SQL theme={null}
SELECT y, LAG(y)
    OVER (ORDER BY y) AS wf
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "wf" |
|-----|------|
|   1 |      |
|   2 |    1 |
|   3 |    2 |
|   4 |    3 |
|   5 |    4 |
|   6 |    5 |
|   7 |    6 |
|   8 |    7 |
|   9 |    8 |
|  10 |    9 |
```

**Calculate Lag Using an Offset**

In this example, the offset value `5` changes the number of rows back from the current row from which to return a value.

```sql SQL theme={null}
SELECT y, LAG(y, 5)
    OVER (ORDER BY y) AS wf
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "wf" |
|-----|------|
|   1 |      |
|   2 |      |
|   3 |      |
|   4 |      |
|   5 |      |
|   6 |    1 |
|   7 |    2 |
|   8 |    3 |
|   9 |    4 |
|  10 |    5 |
```

### LAST\_VALUE

Returns the last value in the ordered result set. 

**Syntax**

```sql SQL theme={null}
LAST_VALUE ( expression )
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                         |
| ------------ | ------------- | ----------------------------------------------------------------------- |
| `expression` | Any           | The column or expression value to use for the `LAST_VALUE` calculation. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example uses a table with generated numbers from one to 10. Of this group, the function determines the last value from the ordering.

```sql SQL theme={null}
SELECT y, LAST_VALUE(y)
    OVER (ORDER BY y) AS last_value
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "last_value" |
|-----|--------------|
|   1 |            1 |
|   2 |            2 |
|   3 |            3 |
|   4 |            4 |
|   5 |            5 |
|   6 |            6 |
|   7 |            7 |
|   8 |            8 |
|   9 |            9 |
|  10 |           10 |
```

### LEAD

Returns a row forward from the current row (one row forward unless you specify a different number value).

**Syntax**

```sql SQL theme={null}
LEAD (value_expr [, offset ])
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                                                                                      |
| ------------ | ------------- | ------------------------------------------------------------------------------------------------------------------------------------ |
| `value_expr` | Any           | The value to use for the `LEAD` calculation.                                                                                         |
| `offset`     | Numeric       | Optional. <br />This number determines how many rows forward from the current row to return a value. <br />The default value is `1`. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Examples**

**Calculate the Lead Value Using Basic Numbers**

This example uses a table with generated numbers from one to 10. Of this group, the `LEAD` function returns the last value for the specified column `y`.

```sql SQL theme={null}
SELECT y, LEAD(y)
    OVER (ORDER BY y) AS lead_value
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "lead_value" |
|-----|--------------|
|   1 |            2 |
|   2 |            3 |
|   3 |            4 |
|   4 |            5 |
|   5 |            6 |
|   6 |            7 |
|   7 |            8 |
|   8 |            9 |
|   9 |           10 |
|  10 |              |
```

**Calculate the Lead Value Using an Offset**

In this example, using a table of generated numbers with values from one to 10, specify the offset value `5` in the `LEAD` function to change the number of rows forward from the current row to return a value.

```sql SQL theme={null}
SELECT y, LEAD(y, 5)
    OVER (ORDER BY y) AS lead_value
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "lead_value" |
|-----|--------------|
|   1 |            6 |
|   2 |            7 |
|   3 |            8 |
|   4 |            9 |
|   5 |           10 |
|   6 |              |
|   7 |              |
|   8 |              |
|   9 |              |
|  10 |              |
```

### NTH\_VALUE

Returns the `n`-th value in the ordered result set. 

**Syntax**

```sql SQL theme={null}
NTH_VALUE (expr, offset)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                                                                             |
| ------------ | ------------- | --------------------------------------------------------------------------------------------------------------------------- |
| `expr`       | Any           | The column or expression from which to retrieve a value.                                                                    |
| `offset`     | Numeric       | A positive integer that indicates the row number, starting from the first row in the window. This integer must be positive. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Examples**

**Calculate the Fifth Value Using Bounded Rows**

This example uses a table with generated numbers from one to 10. Of this group, the `NTH_VALUE` function returns the fifth value for the specified column `y`.

For the first four rows, the window contains fewer than five rows, so the function returns NULL because the fifth value is not yet available.

```sql SQL theme={null}
SELECT y, NTH_VALUE(y, 5)
    OVER (ORDER BY y) AS nth
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "nth" |
|-----|-------|
|   1 |       |
|   2 |       |
|   3 |       |
|   4 |       |
|   5 |     5 |
|   6 |     5 |
|   7 |     5 |
|   8 |     5 |
|   9 |     5 |
|  10 |     5 |
```

**Calculate the Fifth Value Using Unbounded Rows**

To include future rows in the window frame, you can modify the window frame as shown in this example.

```sql SQL theme={null}
SELECT y, NTH_VALUE(y, 5)
	OVER ( ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS nth
FROM
	(SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "nth" |
|-----|-------|
|   1 |     5 |
|   2 |     5 |
|   3 |     5 |
|   4 |     5 |
|   5 |     5 |
|   6 |     5 |
|   7 |     5 |
|   8 |     5 |
|   9 |     5 |
|  10 |     5 |
```

### PERCENTILE

Returns the value corresponding to the specified percentile (0 ≤ `n` ≤ 1) within the group.

The set of values is treated as a continuous distribution, so it is possible that the computed value cannot appear in the result set. 

**Syntax**

```sql SQL theme={null}
PERCENTILE (expr, percentile)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                                                            |
| ------------ | ------------- | ---------------------------------------------------------------------------------------------------------- |
| `expr`       | Any           | The column or expression to use for the `PERCENTILE` calculation.                                          |
| `percentile` | Float         | A percentile (0 ≤ `n` ≤ 1) to determine the value to return by its position within the ordering of `expr`. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example finds the value at approximately 90 percent of the range of the expression values. For the source data, use a table of generated numbers from one to 10.

The value returns `9.1` because the window column starts at `1`. It would be `9` if the column started at `0`.

```sql SQL theme={null}
SELECT y, PERCENTILE(y, 0.9)
    OVER (ORDER BY y) AS percentile_value
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" | "percentile_value" |
|-----|--------------------|
|   1 |                9.1 |
|   2 |                9.1 |
|   3 |                9.1 |
|   4 |                9.1 |
|   5 |                9.1 |
|   6 |                9.1 |
|   7 |                9.1 |
|   8 |                9.1 |
|   9 |                9.1 |
|  10 |                9.1 |
```

### PERCENT\_RANK

Returns a percentage value for each row representing the percentage of values less than the current group, excluding the highest value. The highest value in a group is always 1. 

**Syntax**

```sql SQL theme={null}
PERCENT_RANK ()
    OVER ( [ PARTITION BY part_list ] ORDER BY order_list )
```

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example generates the percent rank of the table with ten generated numbers from one to 10.

```sql SQL theme={null}
SELECT y, PERCENT_RANK()
    OVER (ORDER BY y) AS percent_rank
    FROM (SELECT c1 AS y FROM sys.dummy10);
```

*Output*

```none Text theme={null}
| "y" |   "percent_rank"   |
|-----|--------------------|
|   1 |                0.0 |
|   2 | 0.1111111111111111 |
|   3 | 0.2222222222222222 |
|   4 | 0.3333333333333333 |
|   5 | 0.4444444444444444 |
|   6 | 0.5555555555555556 |
|   7 | 0.6666666666666666 |
|   8 | 0.7777777777777778 |
|   9 | 0.8888888888888888 |
|  10 |                1.0 |
```

### RANK

Assigns a rank number to each row in the result set with equal values having the same number. There can be gaps between ranks. Results are similar to the ranking used in sporting events. 

**Syntax**

```sql SQL theme={null}
RANK ()
    OVER ( [ PARTITION BY part_list ] ORDER BY order_list )
```

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example ranks a series of ten random numbers. Use the [RAND](/math-functions-and-operators) function to generate the random numbers.

```sql SQL theme={null}
WITH RandomNumbers AS (
    SELECT RAND() AS num
    FROM sys.dummy10
)
SELECT num, RANK() OVER (ORDER BY num) AS n
FROM RandomNumbers;
```

*Output*

```none Text theme={null}
|  num                |  n  |
| ------------------- | --- |
| 0.0003210869634629  | 1   |
| 0.03250125317575761 | 2   |
| 0.09413438896602266 | 3   |
| 0.130728439355569   | 4   |
| 0.376078282337459   | 5   |
| 0.5049826734614208  | 6   |
| 0.5642048500743658  | 7   |
| 0.5989180494879046  | 8   |
| 0.9077625673606013  | 9   |
| 0.9428422123895421  | 10  |
```

### RATIO\_TO\_REPORT

Computes the ratio of a value to the sum of the set of values. For example, the `RATIO_TO_REPORT` function calculates the ratio of the salary of an employee relative to the total salaries in the department where the employee belongs.  

**Syntax**

```sql SQL theme={null}
RATIO_TO_REPORT ( ratio_expression )
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument**       | **Data Type** | **Description**                                                                   |
| ------------------ | ------------- | --------------------------------------------------------------------------------- |
| `ratio_expression` | Numeric       | A column or expression to calculate the ratio for each row compared to the total. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example shows a ratio of each salary of an individual employee to the total salaries.

```sql SQL theme={null}
SELECT name, salaries, RATIO_TO_REPORT(salaries)
    OVER (ORDER BY salaries) AS ratio
    FROM salaries;
```

*Output*

```none Text theme={null}
|      name       |  salaries |        ratio        |
|-----------------|-----------|---------------------|
| Vasquez, Gustav |   70000.0 | 0.08484848484848485 |
| Ho, Chen        |   75000.0 | 0.09090909090909091 |
| Kiwi, Marie     |  110000.0 | 0.13333333333333333 |
| Smith, Roberts  |  140000.0 |  0.1696969696969697 |
| Porter, Polly   |  180000.0 | 0.21818181818181817 |
| Svenson, Olaf   |  250000.0 | 0.30303030303030304 |
```

### ROW\_NUMBER

Assigns a unique number to each row in the result set. 

**Syntax**

```sql SQL theme={null}
ROW_NUMBER ()
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Examples**

**Determine the Unique Number of Each Row in a Table**

This example assigns a row number to a basic table of fruit prices based on the price order.

```sql SQL theme={null}
SELECT fruit, price, ROW_NUMBER()
OVER (ORDER BY price) AS row_no
    FROM fruits;
```

*Output*

```none Text theme={null}
|  "fruit"   | "price" |"row_no"|
|------------|---------|--------|
| banana     |     0.5 |      1 |
| kiwi       |    0.75 |      2 |
| orange     |     1.5 |      3 |
| mango      |    1.75 |      4 |
| apple      |    1.99 |      5 |
| strawberry |    3.99 |      6 |
```

**Determine the Unique Number of Each Row** **Using a Partitioned Table**

This example uses a table of sales data for two regions. The `ROW_NUMBER` function orders the sales team members by their region and sales amounts.

```sql SQL theme={null}
SELECT
    salesperson,
    region,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY region) AS total_sales_by_region,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank_in_region
FROM
    sales;
```

*Output*

```none Text theme={null}
| "salesperson" | "region" | "sales_amount" | "total_sales_by_region" | "sales_rank_in_region" |
|---------------|----------|----------------|-------------------------|------------------------|
| Eve           | North    |           2500 |                    5000 |                      1 |
| Bob           | North    |           1500 |                    5000 |                      2 |
| Alice         | North    |           1000 |                    5000 |                      3 |
| Dave          | South    |           3000 |                    5000 |                      1 |
| Carol         | South    |           2000 |                    5000 |                      2 |
```

### ZSCORE

Calculates the z-score of the sample based on the standard deviation (similar to the [STDDEV](/aggregate-functions) aggregate function).

The standard deviation assumes that the data is a sample of a larger set, meaning it corrects for bias.

If your data set is comprehensive and represents an entire group, use the [ZSCOREP](#zscorep) function instead.

**Syntax**

```sql SQL theme={null}
ZSCORE (expr)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                          |
| ------------ | ------------- | ------------------------------------------------------------------------ |
| `expr`       | Numeric       | A column or expression to calculate the standard deviation for each row. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example calculates the standard deviation in a range of salaries.

```sql SQL theme={null}
SELECT name, salaries, ZSCORE(salaries)
OVER (ORDER BY salaries) AS std_dev
    FROM salaries;
```

*Output*

```none Text theme={null}
|      name       | salaries |      std_dev       |
|-----------------|----------|--------------------|
| Vasquez, Gustav |  70000.0 |                    |
| Ho, Chen        |  75000.0 | 0.7071076862849652 |
| Kiwi, Marie     | 110000.0 | 1.1470789011835154 |
| Smith, Roberts  | 140000.0 | 1.2593337909146212 |
| Porter, Polly   | 180000.0 | 1.4100478781464227 |
| Svenson, Olaf   | 250000.0 |  1.634473657490547 |
```

### ZSCOREP

Calculates the z-score of the sample based on the population standard deviation (similar to the [STDEVP](/aggregate-functions) aggregate function).

The population standard deviation assumes that the data contains the entire population, meaning there is no correction for bias. Use it when your data set is comprehensive and represents the entire group (i.e. population) you are analyzing.

If your data set is a sample of a larger set, use the [ZSCORE](#zscore) function.

**Syntax**

```sql SQL theme={null}
ZSCOREP (expr)
    OVER ( [ PARTITION BY part_list ] [ ORDER BY order_list ] )
```

| **Argument** | **Data Type** | **Description**                                                          |
| ------------ | ------------- | ------------------------------------------------------------------------ |
| `expr`       | Numeric       | A column or expression to calculate the standard deviation for each row. |

| **Parameter** | **Data Type** | **Description**                                                                 |
| ------------- | ------------- | ------------------------------------------------------------------------------- |
| `part_list`   | Any           | Specifies the column by which the rows are partitioned for the window function. |
| `order_list`  | Any           | Specifies the column by which the rows are ordered for the window function.     |

**Example**

This example calculates the population standard deviation in a range of salaries.

```sql SQL theme={null}
SELECT name, salaries, ZSCOREP(salaries)
OVER (ORDER BY salaries) AS std_devp
    FROM salaries;
```

*Output*

```none Text theme={null}
|      name       | salaries |      std_devp      |
|-----------------|----------|--------------------|
| Vasquez, Gustav |  70000.0 |                    |
| Ho, Chen        |  75000.0 | 1.0000012800024576 |
| Kiwi, Marie     | 110000.0 | 1.4048790013060097 |
| Smith, Roberts  | 140000.0 | 1.4541534063682966 |
| Porter, Polly   | 180000.0 | 1.5764814535323706 |
| Svenson, Olaf   | 250000.0 | 1.7904761837110903 |
```

## Related Links

[Math Functions and Operators](/math-functions-and-operators)

[General SQL Syntax](/general-sql-syntax)

[Generate Tables Using sys.dummy](/generate-tables-using-sys-dummy)