Using the LAST_VALUE() Function to Get the Value from the Last Row in PostgreSQL

Posted on by Ian

In PostgreSQL the last_value() function returns the value from the last row of the current window frame.

Syntax

The syntax goes like this:

last_value ( value )

The value argument is required. It’s the expression that is evaluated at the last row of the window frame.

Example

Here’s a quick example to demonstrate:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice ) OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice | last_value 
----------+---------------------------------+--------------+------------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |       9.99
     1003 | Hammock                         |        10.00 |      10.00
     1001 | Long Weight (green)             |        11.99 |      11.99
     1004 | Tea Pot                         |        12.45 |      12.45
     1001 | Long Weight (blue)              |        14.75 |      14.75
     1001 | Left handed screwdriver         |        25.99 |      25.99
     1001 | Right handed screwdriver        |        25.99 |      25.99
     1002 | Sledge Hammer                   |        33.49 |      33.49
     1003 | Straw Dog Box                   |        55.99 |      55.99
     1003 | Chainsaw                        |       245.00 |     245.00

In this case we’re getting the last value from the productprice column. That’s because I passed productprice as the argument to the last_value() function.

At first glance, this result may seem superfluous, because the last_value column seems to have simply copied its value from the same row of the productprice column. “That’s not the last row!” I hear you say. True, it’s not the last row of the whole result set, but it is the last row of the current window frame.

By default the window frame changes as the query progresses, and so the last row of the current frame ends up being the current row. That’s because the default frame is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

When we use an ORDER BY clause, this sets the frame to be all rows from the partition start up through the current row’s last ORDER BY peer. However, if we omit the ORDER BY clause, all rows of the partition are included in the window frame, because all rows become peers of the current row.

Removing the ORDER BY Clause

If we remove the ORDER BY clause from the above example last_value() will return the last value of the result set, because all rows are considered peers of the current row:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice ) OVER ( )
FROM products;

Result:

 vendorid |           productname           | productprice | last_value 
----------+---------------------------------+--------------+------------
     1001 | Left handed screwdriver         |        25.99 |      12.45
     1001 | Right handed screwdriver        |        25.99 |      12.45
     1001 | Long Weight (blue)              |        14.75 |      12.45
     1001 | Long Weight (green)             |        11.99 |      12.45
     1002 | Sledge Hammer                   |        33.49 |      12.45
     1003 | Chainsaw                        |       245.00 |      12.45
     1003 | Straw Dog Box                   |        55.99 |      12.45
     1003 | Hammock                         |        10.00 |      12.45
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |      12.45
     1004 | Tea Pot                         |        12.45 |      12.45

Changing the ORDER BY Clause

Here’s what happens when we change the ORDER BY clause to sort by a different column:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice ) OVER ( ORDER BY vendorid )
FROM products;

Result:

 vendorid |           productname           | productprice | last_value 
----------+---------------------------------+--------------+------------
     1001 | Left handed screwdriver         |        25.99 |      11.99
     1001 | Right handed screwdriver        |        25.99 |      11.99
     1001 | Long Weight (blue)              |        14.75 |      11.99
     1001 | Long Weight (green)             |        11.99 |      11.99
     1002 | Sledge Hammer                   |        33.49 |      33.49
     1003 | Chainsaw                        |       245.00 |      10.00
     1003 | Straw Dog Box                   |        55.99 |      10.00
     1003 | Hammock                         |        10.00 |      10.00
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |      12.45
     1004 | Tea Pot                         |        12.45 |      12.45

Here, we sorted by the vendorid column and so the last_value() function returned the last value from each vendor.

Change the Window Frame

As mentioned, the default frame is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Let’s use a frame clause to change the window frame:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice ) OVER ( 
        ORDER BY productprice
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
FROM products;

Result:

 vendorid |           productname           | productprice | last_value 
----------+---------------------------------+--------------+------------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |     245.00
     1003 | Hammock                         |        10.00 |     245.00
     1001 | Long Weight (green)             |        11.99 |     245.00
     1004 | Tea Pot                         |        12.45 |     245.00
     1001 | Long Weight (blue)              |        14.75 |     245.00
     1001 | Left handed screwdriver         |        25.99 |     245.00
     1001 | Right handed screwdriver        |        25.99 |     245.00
     1002 | Sledge Hammer                   |        33.49 |     245.00
     1003 | Straw Dog Box                   |        55.99 |     245.00
     1003 | Chainsaw                        |       245.00 |     245.00

This time we get the last value of the whole result set. That’s because I specified ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING as the frame clause.

Let’s change the frame clause:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice ) OVER ( 
        ORDER BY productprice
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
        )
FROM products;

Result:

 vendorid |           productname           | productprice | last_value 
----------+---------------------------------+--------------+------------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |      10.00
     1003 | Hammock                         |        10.00 |      11.99
     1001 | Long Weight (green)             |        11.99 |      12.45
     1004 | Tea Pot                         |        12.45 |      14.75
     1001 | Long Weight (blue)              |        14.75 |      25.99
     1001 | Left handed screwdriver         |        25.99 |      25.99
     1001 | Right handed screwdriver        |        25.99 |      33.49
     1002 | Sledge Hammer                   |        33.49 |      55.99
     1003 | Straw Dog Box                   |        55.99 |     245.00
     1003 | Chainsaw                        |       245.00 |     245.00

This time I changed UNBOUNDED FOLLOWING to 1 FOLLOWING. That brought the window frame to one row past the current row. In other words, our last_value column returns the productprice value from the next row.

Change the Order

Let’s change it to descending order:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice ) OVER ( 
        ORDER BY productprice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
        )
FROM products;

Result:

 vendorid |           productname           | productprice | last_value 
----------+---------------------------------+--------------+------------
     1003 | Chainsaw                        |       245.00 |      55.99
     1003 | Straw Dog Box                   |        55.99 |      33.49
     1002 | Sledge Hammer                   |        33.49 |      25.99
     1001 | Left handed screwdriver         |        25.99 |      25.99
     1001 | Right handed screwdriver        |        25.99 |      14.75
     1001 | Long Weight (blue)              |        14.75 |      12.45
     1004 | Tea Pot                         |        12.45 |      11.99
     1001 | Long Weight (green)             |        11.99 |      10.00
     1003 | Hammock                         |        10.00 |       9.99
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |       9.99

It still gets the value from the next row. The only difference is that everything’s in descending order.

Partition the Results

Let’s add a PARTITION BY clause to partition by vendor:

SELECT 
    v.vendorname,
    p.productname,
    p.productprice,
    last_value( p.productprice ) OVER ( 
        PARTITION BY p.vendorid 
        ORDER BY p.productprice
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
FROM products p INNER JOIN 
vendors v ON p.vendorid = v.vendorid;

Result:

  vendorname   |           productname           | productprice | last_value 
---------------+---------------------------------+--------------+------------
 Mars Supplies | Long Weight (green)             |        11.99 |      25.99
 Mars Supplies | Long Weight (blue)              |        14.75 |      25.99
 Mars Supplies | Right handed screwdriver        |        25.99 |      25.99
 Mars Supplies | Left handed screwdriver         |        25.99 |      25.99
 Randy Roofers | Sledge Hammer                   |        33.49 |      33.49
 Pedal Medals  | Hammock                         |        10.00 |     245.00
 Pedal Medals  | Straw Dog Box                   |        55.99 |     245.00
 Pedal Medals  | Chainsaw                        |       245.00 |     245.00
 Katty Kittens | Bottomless Coffee Mugs (4 Pack) |         9.99 |      12.45
 Katty Kittens | Tea Pot                         |        12.45 |      12.45

We can see that the last_value column resets with each new partition. I included UNBOUNDED FOLLOWING in the frame clause so that it returned the last value of the current partition – not the last value of the whole result set like we saw in an earlier (unpartitioned) example.

As you probably noticed, in this example I also ran a SQL join between the products table and the vendors table in order to get each vendor’s name instead of just the ID.

Removing the OVER Clause

The OVER clause is required when using nonaggregate window functions such as last_value(). Removing it results in an error:

SELECT
    vendorid,
    productname,
    productprice,
    last_value( productprice )
FROM products;

Result:

ERROR:  window function last_value requires an OVER clause
LINE 5:     last_value( productprice )
            ^