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

> Reference material for NTH_VALUE window function

# NTH_VALUE OVER

Returns the value of an expression evaluated at the Nth row of the window frame. Returns `NULL` if the frame contains fewer than N rows.

For more information on usage, please refer to [Window Functions](/reference-sql/functions-reference/window).

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
NTH_VALUE( <expression>, <n> ) OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ASC|DESC] [ <frame_clause> ] )
```

## Parameters

| Parameter        | Description                                                                                                | Supported input types |
| :--------------- | :--------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<expression>`   | The value to evaluate and return at position N in the window frame.                                        | Any                   |
| `<n>`            | The 1-based position within the window frame. Must be a positive integer.                                  | `INTEGER`, `BIGINT`   |
| `<partition_by>` | An expression used for the `PARTITION BY` clause.                                                          | Any                   |
| `<order_by>`     | An expression used for the `ORDER BY` clause.                                                              | Any                   |
| `<frame_clause>` | An optional window frame specification such as `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`. | —                     |

## Return Type

Same as the input type of `<expression>`.

## Remarks

* `<n>` must be greater than or equal to `1`. Passing `0` or a negative value raises an error.
* Returns `NULL` when N exceeds the number of rows in the window frame.
* This function respects `NULL` values, and results will be ordered with default null ordering `NULLS LAST` unless otherwise specified in the `ORDER BY` clause.

## Example

The following example returns the second-lowest `maxpoints` value within each `leveltype`, repeated for every row in that group:

<div className="query-window">
  ```
  SELECT
      level,
      leveltype,
      maxpoints,
      NTH_VALUE(maxpoints, 2) OVER (
          PARTITION BY leveltype
          ORDER BY maxpoints
          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS second_lowest
  FROM levels
  ORDER BY leveltype, maxpoints;
  ```

  | level <span>int null</span> | leveltype <span>text null</span> | maxpoints <span>int null</span> | second\_lowest <span>int null</span> |
  | :-------------------------- | :------------------------------- | :------------------------------ | :----------------------------------- |
  | 6                           | Drift                            | 80                              | 250                                  |
  | 9                           | Drift                            | 250                             | 250                                  |
  | 1                           | FastestLap                       | 20                              | 40                                   |
  | 3                           | FastestLap                       | 40                              | 40                                   |
  | 7                           | FastestLap                       | 70                              | 40                                   |
  | 8                           | FastestLap                       | 100                             | 40                                   |
  | 2                           | FirstToComplete                  | 30                              | 100                                  |
  | 4                           | FirstToComplete                  | 100                             | 100                                  |
  | 5                           | FirstToComplete                  | 150                             | 100                                  |
  | 10                          | FirstToComplete                  | 500                             | 100                                  |

  <p><span>Rows: 10</span><span>Execution time: 11.07ms</span></p>
</div>