​
Overview

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 as follows:

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

​
Parameters

  • expression: input’s column or expression values that returns a single value. It represents the value you want to retrieve from the first row of the sorted partition
  • PARTITION BY: optional clause, which divides the result set into partitions to which the LAST_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 website.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  rating text,
  length int
);
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 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 shortest_film_in_rating
FROM film;

By running the code above, we will get the following output:

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