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:

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

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:

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:

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