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

# NTH_VALUE()

## Overview

The `NTH_VALUE()` is a window function that allows you to access the value from the nth row within a specified window frame.

## Syntax

The syntax for this function is as follows:

```sql theme={null}
NTH_VALUE (value, n) OVER ( 
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    RANGE BETWEEN start_value AND end_value 
)
```

## Parameters

* `value`: column or expression for which you want to retrieve the value
* `n`: positive integer (greater than zero) that determines the row number within the window frame from which to retrieve the value
* `PARTITION BY`: optional clause, which divides the result set into partitions to which the `NTH_VALUE()` function is applied (if omitted, the entire result set is treated as a single partition)
* `ORDER BY`: order of rows in each partition to which the function is applied
* `RANGE BETWEEN`: range-based window frame relative to the current row

## Example

For the needs of this section we will use a simplified version of the `film` table from the Pagila database, containing only the `title`, `length` and `rating` columns. The complete schema for the `film` table can be found on the
<a href="https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/pagila/pagila/" target="_blank">Pagila</a> database website.

```sql theme={null}
DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating text
);

INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 'PG-13'),
  ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
  ('DANGEROUS UPTOWN', 121, 'PG'),
  ('KILL BROTHERHOOD', 54, 'G'),
  ('HALLOWEEN NUTS', 47, 'PG-13'),
  ('HOURS RAGE', 122, 'NC-17'),
  ('PIANIST OUTFIELD', 136, 'NC-17'),
  ('PICKUP DRIVING', 77, 'G'),
  ('INDEPENDENCE HOTEL', 157, 'NC-17'),
  ('PRIVATE DROP', 106, 'PG'),
  ('SAINTS BRIDE', 125, 'G'),
  ('FOREVER CANDIDATE', 131, 'NC-17'),
  ('MILLION ACE', 142, 'PG-13'),
  ('SLEEPY JAPANESE', 137, 'PG'),
  ('WRATH MILE', 176, 'NC-17'),
  ('YOUTH KICK', 179, 'NC-17'),
  ('CLOCKWORK PARADISE', 143, 'PG-13');
```

The query below uses the `NTH_VALUE()` function to retrieve the title of the film with the second shortest duration, partitioning results by rating and ordering by length:

```sql theme={null}
SELECT
  title,
  length,
  rating,
  NTH_VALUE(title, 2) OVER (
    PARTITION BY rating
    ORDER BY
      length ASC
  ) AS second_shortest_film_in_rating
FROM film;
```

The above query will show the following table:

```bash theme={null}
|   title             |length  |rating  |  second_shortest_film_in_rating  |
|---------------------|--------|--------|----------------------------------|
| KILL BROTHERHOOD    | 54     | G      | NULL                             |
| PICKUP DRIVING      | 77     | G      | PICKUP DRIVING                   |
| SAINTS BRIDE        | 125    | G      | PICKUP DRIVING                   |
| HOURS RAGE          | 122    | NC-17  | NULL                             |       
| FOREVER CANDIDATE   | 131    | NC-17  | FOREVER CANDIDATE                |      
| PIANIST OUTFIELD    | 136    | NC-17  | FOREVER CANDIDATE                |  
| CHRISTMAS MOONSHINE | 150    | NC-17  | FOREVER CANDIDATE                |
| INDEPENDENCE HOTEL  | 157    | NC-17  | FOREVER CANDIDATE                |
| WRATH MILE          | 176    | NC-17  | FOREVER CANDIDATE                |
| YOUTH KICK          | 179    | NC-17  | FOREVER CANDIDATE                |
| PRIVATE DROP        | 106    | PG     | NULL                             |
| DANGEROUS UPTOWN    | 121    | PG     | DANGEROUS UPTOWN                 |
| SLEEPY JAPANESE     | 137    | PG     | DANGEROUS UPTOWN                 |
| HALLOWEEN NUTS      | 47     | PG-13  | NULL                             |
| ATTRACTION NEWTON   | 83     | PG-13  | ATTRACTION NEWTON                |
| MILLION ACE         | 142    | PG-13  | ATTRACTION NEWTON                |
| CLOCKWORK PARADISE  | 143    | PG-13  | ATTRACTION NEWTON                |
```