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:
Contact Us