Cloud

last_value

The last_value() is a window function that retrieves the last value in an ordered set of values within a specified partition.

Syntax

The syntax for this function is:

LAST_VALUE(expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    RANGE BETWEEN start_value AND end_value
)

Parameters

expression: Column or expression that returns a single value. Represents the value to retrieve from the first row of the sorted partition.
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: Order of rows in each partition to which the function is applied.
RANGE BETWEEN: 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 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 following query uses the last_value() function to retrieve the title of the film with the longest duration, partitioning results by rating and ordering by length.

SELECT
  title,
  length,
  rating,
  LAST_VALUE(title) OVER (
    PARTITION BY rating
    ORDER BY
      length ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS longest_film_in_rating
FROM film;

The query returns:

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