FIRST_VALUE()

Overview

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

Syntax

The syntax for this function is as follows:

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

Parameters

• expression: target column or expression that the function operates on
• PARTITION BY: optional clause, which divides the result set into partitions to which the FIRST_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 FIRST_VALUE() function to retrieve the title of the film with the shortest duration, partitioning results by rating and ordering by length.

SELECT
  title,
  length,
  rating,
  FIRST_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 executing the code above, we will get the following output:

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