Understanding the LAST_VALUE() Function in SQL

In SQL databases, LAST_VALUE() is a window function that returns the last value in an ordered set of values. It allows us to get a value from the last row of a query result set or partition.

You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because in many/most DBMSs, the default window frame ends with the current row.

Example

This example demonstrates how the SQL LAST_VALUE() function works:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice ) OVER ( 
        ORDER BY ProductPrice 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS "LAST_VALUE"
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 example should work in most RDBMSs. I ran that example in MySQL, SQL Server, and PostgreSQL without error.

Here, I used ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to define the window frame. I used this definition to ensure that the window covers the whole partition/query result set. The UNBOUNDED FOLLOWING part ensures that we get the last value from the partition/result set, and not just the last value up until the current row.

In many DBMSs, the default window frame when using an ORDER BY clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The CURRENT ROW part means that LAST_VALUE() would simply pick its value from the current row. In our case, it would have resulted in the ProductPrice column being duplicated, which wouldn’t really be that helpful (and probably would’ve made the example quite confusing).

Here’s an example of what I mean:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice ) OVER ( 
        ORDER BY ProductPrice 
        ) AS "LAST_VALUE"
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, the “last row” is always the current row as it progresses through the result set. Therefore, the LAST_VALUE() function returns the value of the current row, which isn’t really that helpful.

This only happens when we use an ORDER BY clause in the window function. Removing the ORDER BY clause can resolve the issue, but this will depend on your DBMS. Some DBMSs (such as SQL Server) require the ORDER BY clause in the OVER clause, and return an error if we remove it (examples later).

You DBMS may also require an ORDER BY clause when using the ROWS/RANGE clause.

Partitioning the Result Set

We can use a PARTITION BY clause to partition the result set by a given column. When we do this, the LAST_VALUE() function returns the last value in the current partition.

Let’s partition by vendor:

SELECT
    v.VendorName,
    p.ProductName,
    p.ProductPrice,
    LAST_VALUE( ProductPrice ) OVER ( 
        PARTITION BY p.VendorId
        ORDER BY p.ProductPrice 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS "LAST_VALUE"
FROM Products p 
INNER JOIN Vendors v
ON v.VendorId = p.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 | Left handed screwdriver         |        25.99 |      25.99 |
| Mars Supplies | Right 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 returns the last value of each partition.

By the way, in this example I used a SQL join to join the Products and Vendors table so that the vendor names are returned instead of just their IDs.

The `OVER` Clause is Required

The OVER clause is generally a required clause when using window functions such as LAST_VALUE() in SQL. Here’s what happens if we remove it:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice )
FROM Products;

Result in SQL Server:

Msg 10753, Level 15, State 1, Line 5
The function 'LAST_VALUE' must have an OVER clause.

Result in MySQL:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Products' at line 6

Result in PostgreSQL:

window function last_value requires an OVER clause

The `ORDER BY` Clause is Sometimes Required (Depends on the DBMS)

Some DBMSs require that the OVER clause includes an ORDER BY clause. Removing the ORDER BY clause in such DBMSs results in an error.

Here’s what happens when I remove the ORDER BY clause in SQL Server:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice ) OVER( )
FROM Products;

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'LAST_VALUE' must have an OVER clause with ORDER BY.

But here’s what happens when I run the same code in MySQL:

+----------+---------------------------------+--------------+------------------------------------+
| VendorId | ProductName                     | ProductPrice | LAST_VALUE( ProductPrice ) OVER( ) |
+----------+---------------------------------+--------------+------------------------------------+
|     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 |
|     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |                              12.45 |
|     1003 | Hammock                         |        10.00 |                              12.45 |
|     1004 | Tea Pot                         |        12.45 |                              12.45 |
+----------+---------------------------------+--------------+------------------------------------+

No error. I get the same result in PostgreSQL.

Example

Partitioning the Result Set

The OVER Clause is Required

The ORDER BY Clause is Sometimes Required (Depends on the DBMS)

The `OVER` Clause is Required

The `ORDER BY` Clause is Sometimes Required (Depends on the DBMS)