How to Use PostgreSQL FIRST_VALUE Function

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

Introduction

In this article we will learn how to use PostgreSQL FIRST_VALUE function. The only prerequisite is that you should have PostgreSQL installed and running.

What is PostgreSQL FIRST_VALUE Function

The PostgreSQL FIRST_VALUE will be retrieving a value after evaluating the first row of a result set’s sorted partition.

Below is the syntax we will using for the PostgreSQL function FIRST_VALUE().

1
2
3
4
5
6
FIRST_VALUE ( expression )
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [specified_rows_range_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 first row of a result set’s sorted partition. An individual or single value must be returned.

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

  3. To sort the order of the table rows in every partition we used ORDER BY clause, to where the FIRST_VALUE clause is defined.

  4. The specified_rows_range_clause will fine tune the limits of the table rows in every partition by specifying the beginning and ending positions within the partition.

Using the PostgreSQL FIRST_VALUE Function

We will now use the FIRST_VALUE function in some examples to be able to further understand on how to use it in real world examples.

Before we proceed with the examples we have to create a table to be used with our examples.

Use the following statement to create our 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 use 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 FIRST_VALUE in an Entire Result Set.

The below statement will be retrieving all team and also the team having the lowest rating using the FIRST_VALUE()

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

Let’s discuss the above query.

  • The entire result set will be treated as a an individual partition since we by pass the PARTITION BY in the FIRST_VALUE.
  • We then sort the order of the team’s rating in an ascending order using the ORDER BY clause.
  • The value of the first row is selected in the column team_name as we specify the FIRST_VALUE in the entire result set.

Below is the result of the above query.

1
2
3
4
5
6
7
8
9
10
11
12
 team_id | team_name  | group_id | rating | highest_team_rating
---------+------------+----------+--------+---------------------
       1 | Armada     |        1 |   8.00 | Armada
       6 | ZeroArc    |        2 |   8.00 | Armada
       7 | Panda      |        3 |   8.00 | Armada
       8 | YinYang    |        3 |   8.00 | Armada
       3 | Mineski    |        1 |   8.00 | Armada
       5 | WinterWolf |        2 |   8.00 | Armada
       2 | TeamSolo   |        1 |   9.00 | Armada
       9 | GreatWall  |        3 |   9.00 | Armada
       4 | Hydra      |        2 |   9.00 | Armada
(9 rows)

Example on How to Use PostgreSQL FIRST_VALUE in Partition

In this example the below statement will be using the FIRST_VALUE to retrieve all the teams grouped together via team group, also each of the team group will retrieve the team having the lowest rating.

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

Let’s discuss the above query in more detail.

  • We can distribute the team by their respective team group using the clause PARTITION By.
  • ORDER By will sort the teams in each respective group or partition in an ascending order – from lowest to highest rating.

  • The specified_rows_range_clause (RANGE BETWEEN UNDBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) will be specifies the frame for every partitions that will start from the beginning of the 1st row and will be ending at the least row.

  • The FIRST_VALUE will be applied separately to each partitions.

The result should look something like the chase

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 | Armada
       3 | Mineski    |        1 |   8.00 | Armada
       2 | TeamSolo   |        1 |   9.00 | Armada
       5 | WinterWolf |        2 |   8.00 | WinterWolf
       6 | ZeroArc    |        2 |   8.00 | WinterWolf
       4 | Hydra      |        2 |   9.00 | WinterWolf
       7 | Panda      |        3 |   8.00 | Panda
       8 | YinYang    |        3 |   8.00 | Panda
       9 | GreatWall  |        3 |   9.00 | Panda
(9 rows)

Conclusion

In this article we learn how to use the PostgreSQL FIRST_VALUE() function. We have explained the use case as well as gone over a demo and we hope you can transfer what you’ve learned here to your use case.

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.