How to Use PostgreSQL LAST_VALUE Function
In this article we will learn how to use PostgreSQL LAST_VALUE function. Although the knowledge will apply to most SQL database systems we will be doing the demo in PostgreSQL so our prerequisite if you want to follow along is that you have PostgreSQL installed and running.
What is PostgreSQL LAST_VALUE Function
LAST_VALUE will be retrieving a value after evaluating the last row of a result set’s sorted partition.
Below is the syntax we will using for the PostgreSQL function
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
Let’s explain the above syntax part by part.
expressioncould be a column, an actual expression, or a subquery that will be assessed against the value of the tables last row of a result set’s sorted partition. An individual or single value must be returned.
PARTITION BYwill be dividing the table rows into partitions to where the
FIRST_VALUEfunction is defined
ORDER BYwill sort the table row’s order in every partition to where the
LAST_VALUEfunction is defined.
To be able to specify the rows’s subset in the present position to where the function is applied we use the clause
Examples of PostgreSQL LAST_VALUE() Function
Before we proceed with demonstrating how we can use
LAST_VALUE() function in a real life application we have to create a table that we can use for this purpose.
Use the following statement to be able to create our database tables namely
group_id serial PRIMARY KEY,
group_name VARCHAR (255) NOT NULL
CREATE TABLE team (
team_id serial PRIMARY KEY,
team_name VARCHAR (255) NOT NULL,
rating DECIMAL (11, 2),
group_id INT NOT NULL,
FOREIGN KEY (group_id) REFERENCES team_groups (group_id)
To insert a sample dataset we will be using the following queries.
INSERT INTO team (team_name, group_id,rating)
('Armada', 1, 8),
('TeamSolo', 1, 9),
('Mineski', 1, 8),
('Hydra', 2, 9),
('WinterWolf', 2, 8),
('ZeroArc', 2, 8),
('Panda', 3, 8),
('YinYang', 3, 8),
('GreatWall', 3, 9);
Example on How to Use PostgreSQL LAST_VALUE in an Entire Result Set.
The statement used in this example will be using the PostgreSQL
LAST_VALUE() to retrieve all the teams and the team having the highest points.
ORDER BY rating
UNBOUNDED PRECEDING AND
Let’s discuss the above statement further.
We have taken out the clause
PARTITION BYin the
LAST_VALUE()function thus, the entire result set will be treated as an individual partition by simply using the function
To simply sort the team in an ascending order via
ratingfield we will use the clause
Finally we use the
LAST_VALUE()to select within partition of the result set it’s the beginning row.
the result should resemble something like this:
1 | Armada | 8.00 | Hydra
6 | ZeroArc | 8.00 | Hydra
7 | Panda | 8.00 | Hydra
8 | YinYang | 8.00 | Hydra
3 | Mineski | 8.00 | Hydra
5 | WinterWolf | 8.00 | Hydra
2 | TeamSolo | 9.00 | Hydra
9 | GreatWall | 9.00 | Hydra
4 | Hydra | 9.00 | Hydra
Example on How to Use PostgreSQL LAST_VALUE in Partition
In this example we will be using the function
LAST_VALUE to retrieve all of the teams together with highest team rating for it’s respective team group.
PARTITION BY group_id
ORDER BY rating
UNBOUNDED PRECEDING AND
The above statement can be interpreted as follows.
To divide the rows by its respective group id into three groups or partitions as defined by id group one, two and three respectively we then use the clause
SORT BYto sort teams in each of the team group in an ascending order.
specified_rows_range_clauseor also known as
RANGE BETWEEN UNDBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGwill specify the frame for the beginning of the row and the ending of the latest row in every partition.
Using the clause
LAST_VALUE()will be applied separately to each and every partition and will also retrieve the team name of the every partition’s last row.
The result should look something like the following.
1 | Armada | 1 | 8.00 | TeamSolo
3 | Mineski | 1 | 8.00 | TeamSolo
2 | TeamSolo | 1 | 9.00 | TeamSolo
5 | WinterWolf | 2 | 8.00 | Hydra
6 | ZeroArc | 2 | 8.00 | Hydra
4 | Hydra | 2 | 9.00 | Hydra
7 | Panda | 3 | 8.00 | GreatWall
8 | YinYang | 3 | 8.00 | GreatWall
9 | GreatWall | 3 | 9.00 | GreatWall
In this article we learn how to use the PostgreSQL
LAST_VALUE() function. We showed you a demo of using the LAST_VALUE function and hope you can apply what you’ve learned to your specific circumstance. Thank you for reading the knowledge base tutorials at Object Rocket.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started