# NTH\_VALUE

Returns an expression evaluated at the nth row of the window frame.

If there are fewer than n rows in the frame, returns `NULL`. For an overview of window functions in SingleStore, see [Window Functions Guide](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-window-functions.md).

## Syntax

```
NTH_VALUE ( expression, n ) OVER (
    [PARTITION BY (col | expr), ...]
    [ORDER BY (col | expr), ...]
    [frame_clause]
)

```

## Arguments

* expression: any expression. This may be a column name, the result of another function, or a math operation.
* n: an integer or integer expression.

## Return Type

The result type of expression

## Examples

```sql
CREATE TABLE nth_example (a int, b int);

INSERT INTO nth_example VALUES (1, 2), (2, 2), (3, 3), (4, 3);

```

```sql
SELECT a, NTH_VALUE(a, 2) OVER (ORDER BY a) FROM nth_example;


```

```output

+------+-----------------------------------+
| a    | NTH_VALUE(a, 2) OVER (ORDER BY a) |
+------+-----------------------------------+
|    1 |                              NULL |
|    2 |                                 2 |
|    3 |                                 2 |
|    4 |                                 2 |
+------+-----------------------------------+
```

```sql
SELECT a, NTH_VALUE(a, case when a > 2 then 1 else 2 end) OVER
        (ORDER BY a rows between unbounded preceding and unbounded following) as nth_value FROM nth_example;


```

```output

+------+-----------+
| a    | nth_value |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         1 |
|    4 |         1 |
+------+-----------+
```

***

Modified at: February 16, 2023

Source: [/db/v9.1/reference/sql-reference/window-functions/nth-value/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/nth-value/)

(An index of the documentation is available at /llms.txt)