PostgreSQL – LAST_VALUE Function

In PostgreSQL, the LAST_VALUE() function returns the last value in an ordered partition of the query result set .

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

Let’s analyze the above syntax:

• The expression is single value integer used to evaluated against the value of the last row in an ordered partition of the query result set. It can be an expression, column, or subquery.
• The PARTITION BY clause is used to divide the query result into sets.
• The ORDER BY clause is used to order the result query set in each partition.
• The frame_clause is used to specify the subset of rows in the current partition.

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 query uses the LAST_VALUE() function to return all Mammals together with the mammal that has the highest lifespan:

SELECT 
    mammal_id,
    mammal_name,
    lifespan,
    LAST_VALUE(mammal_name) 
    OVER(
        ORDER BY lifespan
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) longest_lifespan
FROM 
    Mammals;

Output:

Example 2:

The following query uses the LAST_VALUE() function to return all mammals together with the mammal with longest lifespan per animal group:

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

Output:

My Personal Notes arrow_drop_up

Save