Introduction to PostgreSQL FIRST_VALUE() function

TheFIRST_VALUE() function returns a value evaluated against the first row in a sorted partition of a result set.

The following is the syntax of theFIRST_VALUE() function:

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

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

In this syntax:

expression

Theexpression can be an expression, column, or subquery evaluated against the value of the first row of a sorted partition of a result set. Theexpression must return a single value. And it cannot be a window function.

PARTITION BY clause

ThePARTITION BY clause divides rows in a result set into partitions to which theFIRST_VALUE() function is applied.

When you thePARTITION BY clause, theFIRST_VALUE() function treats the whole result set as a single partition.

ORDER BY clause

TheORDER BY clause specifies the sort order of rows in each partition to which theFIRST_VALUE()function is applied.

rows_range_clause

Therows_range_clause further limits the rows within the partition by defining the start and end in the partition

PostgreSQL FIRST_VALUE() function examples

We will use theproducts table created in the window function tutorial for the demonstration:

The data of theproducts table is as follows:

1) Using PostgreSQL FIRST_VALUE() function over a result set example

The following statement uses theFIRST_VALUE() function to return all products and also the product which has the lowest price:

SELECT    product_id,    product_name,    group_id,    price,    FIRST_VALUE(product_name)    OVER(        ORDER BY price    ) lowest_priceFROM    products;

Here is the result set:

In this example:

• Since we skipped thePARTITION BY clause in theFIRST_VALUE() function, the function treated the whole result set as a single partition.
• TheORDER BY clause sorted products by prices from low to high.
• TheFIRST_VALUE() function is applied to the whole result set and picked the value in theproduct_name column of the first row.

2) Using FIRST_VALUE() function over a partition example

This statement uses theFIRST_VALUE() function to return all products grouped by the product group. And for each product group, it returns the product with the lowest price:

SELECT    product_id,    product_name,group_id,    price,    FIRST_VALUE(product_name)    OVER(PARTITION BY group_id        ORDER BY price        RANGE BETWEEN            UNBOUNDED PRECEDING AND            UNBOUNDED FOLLOWING    ) lowest_priceFROM    products;

In this example:

• ThePARTITION BY clause distributed products by product group.
• TheORDER BY clause sorted products in each product group (partition) by prices from low to high.
• TheRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING clause defined the frame in each partition, starting from the first row and ending at the last row.
• TheFIRST_VALUE() function is applied to each partition separately.

In this tutorial, you have learned how to use the PostgreSQLFIRST_VALUE() function to return the first value in a sorted partition of a result set.

Last updated onJuly 10, 2020

On this page

• Introduction to PostgreSQL FIRST_VALUE() function
• PostgreSQL FIRST_VALUE() function examples