PostgreSQL – FIRST_VALUE Function

In PostgreSQL, the FIRST_VALUE() function is used to return the first value in a sorted partition of a result set.

Syntax:
FIRST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Let’s analyze the above syntax:

• The expression is responsible for evaluating the values against the value of the first row of a sorted partition of a query result set. It can be an expression, column, or subquery that mush evaluate a single integer value.
• The PARTITION BY clause is used to divides rows in a result set.
• The ORDER BY clause is used to sort the rows the query result set in each partition.
• The rows_range_clause is used to limits the rows inside the partition by setting up the start and end of the query set partitions.

Example 1:

First, create two tables named Mammals and Animal_groups for the demonstration:

CREATE TABLE Animal_groups (
    animal_id serial PRIMARY KEY,
    animal_name VARCHAR (255) NOT NULL
);

CREATE TABLE Mammals (
    mammal_id serial PRIMARY KEY,
    mammal_name VARCHAR (255) NOT NULL,
    lifespan DECIMAL (11, 2),
    animal_id INT NOT NULL,
    FOREIGN KEY (animal_id) REFERENCES Animal_groups (animal_id)
);

Now insert some data to the table:

INSERT INTO Animal_groups (animal_name)
VALUES
    ('Terrestrial'),
    ('Aquatic'),
    ('Winged');

INSERT INTO Mammals(mammal_name, animal_id, lifespan)
VALUES
    ('Cow', 1, 10),
    ('Dog', 1, 7),
    ('Ox', 1, 13),
    ('Wolf', 1, 11),
    ('Blue Whale', 2, 80),
    ('Dolphin', 2, 5),
    ('Sea Horse', 2, 3),
    ('Octopus', 2, 8),
    ('Bat', 3, 4),
    ('Flying Squirrels', 3, 1),
    ('Petaurus', 3, 2);

The following statement uses the FIRST_VALUE() function to return all mammals and also the mammal which has the lowest lifespan:

SELECT 
    mammal_id,
    mammal_name,
    mammal_id,
    lifespan,
    FIRST_VALUE(mammal_name) 
    OVER(
        ORDER BY lifespan
    ) lowest_lifespan
FROM 
    Mammals;

Output:

Example 2:

The below statement uses the FIRST_VALUE() function to return all mammals grouped by the animal group. And for each animal group, it returns the mammal with the lowest lifespan:

SELECT 
    mammal_id,
    mammal_name,
    mammal_id,
    lifespan,
    FIRST_VALUE(mammal_name) 
    OVER(
    PARTITION BY animal_id
        ORDER BY lifespan
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) lowest_lifespan
FROM 
    Mammals;

Output: