How to Use PostgreSQL LAST_VALUE Function

Written by Data Pilot
September 06, 2019
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

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

The PostgreSQL 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 LAST_VALUE().

1
2
3
4
5
6
LAST_VALUE ( expression )
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [frame_clause]
)

Let’s explain the above syntax part by part.

  1. expression could 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.

  2. Using the PARTITION BY will be dividing the table rows into partitions to where the FIRST_VALUE function is defined

  3. ORDER BY will sort the table row’s order in every partition to where the LAST_VALUE function is defined.

  4. To be able to specify the rows’s subset in the present position to where the function is applied we use the clause frame_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 team_group and team.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE team_groups (
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO team_groups (group_name)
VALUES
   ('Philippines'),
   ('America'),
   ('China');

INSERT INTO team (team_name, group_id,rating)
VALUES
   ('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.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    team_id,
    team_name,
    rating,
    LAST_VALUE(team_name)
    OVER(
        ORDER BY rating
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    ) team_highest_point
FROM
    team;

Let’s discuss the above statement further.

  • We have taken out the clause PARTITION BY in the LAST_VALUE() function thus, the entire result set will be treated as an individual partition by simply using the function LAST_VALUE().

  • To simply sort the team in an ascending order via rating field we will use the clause ORDER BY.

  • 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
2
3
4
5
6
7
8
9
10
11
12
team_id | team_name  | rating | team_highest_point
---------+------------+--------+--------------------
       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
(9 rows)

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    team_id,
    team_name,
   group_id,
    rating,
    LAST_VALUE(team_name)
    OVER(
      PARTITION BY group_id
        ORDER BY rating
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    ) team_highest_rating
FROM
    team;

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 PARTITION BY.

  • We use SORT BY to sort teams in each of the team group in an ascending order.

  • The specified_rows_range_clause or also known as RANGE BETWEEN UNDBOUNDED PRECEDING AND UNBOUNDED FOLLOWING will 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
2
3
4
5
6
7
8
9
10
11
12
 team_id | team_name  | group_id | rating | team_highest_rating
---------+------------+----------+--------+---------------------
       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
(9 rows)

Conclusion

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.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.