Cloud

nth_value

The nth_value() is a window function that accesses the value from the nth row within a specified window frame.

Syntax

The syntax for this function is:

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. Divides the result set into partitions, each processed independently. If omitted, the entire result set is treated as a single partition.
ORDER BY: Optional. Specifies the order of rows in each partition to which the function is applied.
RANGE BETWEEN: Optional. Defines a range-based window frame relative to the current row.

Examples

The following example uses 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 Pagila database website.

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');

This query 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:

SELECT
  title,
  length,
  rating,
  NTH_VALUE(title, 2) OVER (
    PARTITION BY rating
    ORDER BY
      length ASC
  ) AS second_shortest_film_in_rating
FROM film;

This query shows the following table:

|   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                |