Introduction to the LAST_VALUE() Function in MySQL

In MySQL, the LAST_VALUE() function is a window function that returns the value of the given expression from the last row of the window frame.

Syntax

The syntax goes like this:

LAST_VALUE(expr) [null_treatment] over_clause

So it requires an OVER clause, which determines how the rowset is partitioned and ordered before the window function is applied.

The optional null_treatment clause specifies how NULL values are treated. This clause is part of the SQL standard, but MySQL only allows RESPECT NULLS, which is also the default. RESPECT NULLS means that NULL values are considered when calculating results. On the other hand, IGNORE NULLS is parsed, but produces an error (at least as of MySQL 8.0.33).

Example

Here’s a basic example to demonstrate how it works:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) 
        OVER ( 
            ORDER BY Score 
            ROWS UNBOUNDED PRECEDING ) 
    AS "Last Value"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Bruno   | Keep Quiet  |     1 |          1 |
| Cooper  | Keep Quiet  |     8 |          8 |
| Max     | Keep Quiet  |    12 |         12 |
| Bruno   | Fetch Stick |    43 |         43 |
| Bruno   | Wag Tail    |    51 |         51 |
| Cooper  | Wag Tail    |    51 |         51 |
| Cooper  | Fetch Stick |    67 |         67 |
| Max     | Wag Tail    |    87 |         87 |
| Max     | Fetch Stick |    91 |         91 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

In this case, the window frame is ROWS UNBOUNDED PRECEDING and so we simply get the value for the current row.

Let’s modify the window frame:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) 
        OVER ( 
            ORDER BY Score 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) 
    AS "Last Value"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Bruno   | Keep Quiet  |     1 |         91 |
| Cooper  | Keep Quiet  |     8 |         91 |
| Max     | Keep Quiet  |    12 |         91 |
| Bruno   | Fetch Stick |    43 |         91 |
| Bruno   | Wag Tail    |    51 |         91 |
| Cooper  | Wag Tail    |    51 |         91 |
| Cooper  | Fetch Stick |    67 |         91 |
| Max     | Wag Tail    |    87 |         91 |
| Max     | Fetch Stick |    91 |         91 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

This time our frame extends to the end of the rowset, and so we get the last value of the rowset.

Let’s change to another frame:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) 
        OVER ( 
            ORDER BY Score 
            ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) 
    AS "Last Value"
FROM Dogs;

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Bruno   | Keep Quiet  |     1 |          8 |
| Cooper  | Keep Quiet  |     8 |         12 |
| Max     | Keep Quiet  |    12 |         43 |
| Bruno   | Fetch Stick |    43 |         51 |
| Bruno   | Wag Tail    |    51 |         51 |
| Cooper  | Wag Tail    |    51 |         67 |
| Cooper  | Fetch Stick |    67 |         87 |
| Max     | Wag Tail    |    87 |         91 |
| Max     | Fetch Stick |    91 |         91 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

This time we specified the frame start one row before the current row and end one row after. The result is that LAST_VALUE() returns the value from the following row.

Partitioning

This example uses a PARTITION BY clause to partition the results by activity:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) OVER win AS "Last Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS UNBOUNDED PRECEDING
    );

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Bruno   | Fetch Stick |    43 |         43 |
| Cooper  | Fetch Stick |    67 |         67 |
| Max     | Fetch Stick |    91 |         91 |
| Bruno   | Keep Quiet  |     1 |          1 |
| Cooper  | Keep Quiet  |     8 |          8 |
| Max     | Keep Quiet  |    12 |         12 |
| Bruno   | Wag Tail    |    51 |         51 |
| Cooper  | Wag Tail    |    51 |         51 |
| Max     | Wag Tail    |    87 |         87 |
+---------+-------------+-------+------------+
9 rows in set (0.01 sec)

This has caused the LAST_VALUE() function to reset with each partition.

And here’s what happens if we extend the frame:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) OVER win AS "Last Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    );

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Bruno   | Fetch Stick |    43 |         91 |
| Cooper  | Fetch Stick |    67 |         91 |
| Max     | Fetch Stick |    91 |         91 |
| Bruno   | Keep Quiet  |     1 |         12 |
| Cooper  | Keep Quiet  |     8 |         12 |
| Max     | Keep Quiet  |    12 |         12 |
| Bruno   | Wag Tail    |    51 |         87 |
| Cooper  | Wag Tail    |    51 |         87 |
| Max     | Wag Tail    |    87 |         87 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

The `null_treatment` Clause

As mentioned, the optional null_treatment clause specifies how to treat NULL values. The default value for this clause is RESPECT NULLS. Therefore, the above example implicitly uses RESPECT NULLS.

Let’s explicitly specify RESPECT NULLS:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) RESPECT NULLS OVER win AS "Last Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS UNBOUNDED PRECEDING
    );

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Bruno   | Fetch Stick |    43 |         43 |
| Cooper  | Fetch Stick |    67 |         67 |
| Max     | Fetch Stick |    91 |         91 |
| Bruno   | Keep Quiet  |     1 |          1 |
| Cooper  | Keep Quiet  |     8 |          8 |
| Max     | Keep Quiet  |    12 |         12 |
| Bruno   | Wag Tail    |    51 |         51 |
| Cooper  | Wag Tail    |    51 |         51 |
| Max     | Wag Tail    |    87 |         87 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

Specifying IGNORE NULLS produces an error (at least as of MySQL 8.0.33):

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) IGNORE NULLS OVER win AS "Last Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score 
    ROWS UNBOUNDED PRECEDING
    );

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'IGNORE NULLS'

Changing the Order

Here’s what happens when we change the order to descending order:

SELECT 
    DogName,
    Activity,
    Score,
    LAST_VALUE( Score ) OVER win AS "Last Value"
FROM Dogs
WINDOW win AS ( 
    PARTITION BY Activity 
    ORDER BY Score DESC
    ROWS UNBOUNDED PRECEDING
    );

Result:

+---------+-------------+-------+------------+
| DogName | Activity    | Score | Last Value |
+---------+-------------+-------+------------+
| Max     | Fetch Stick |    91 |         91 |
| Cooper  | Fetch Stick |    67 |         67 |
| Bruno   | Fetch Stick |    43 |         43 |
| Max     | Keep Quiet  |    12 |         12 |
| Cooper  | Keep Quiet  |     8 |          8 |
| Bruno   | Keep Quiet  |     1 |          1 |
| Max     | Wag Tail    |    87 |         87 |
| Bruno   | Wag Tail    |    51 |         51 |
| Cooper  | Wag Tail    |    51 |         51 |
+---------+-------------+-------+------------+
9 rows in set (0.00 sec)

Passing a Different Column to `LAST_VALUE()`

In the above examples, the column that we pass to the LAST_VALUE() function is the same one we pass to the ORDER BY clause. But that’s not a requirement. We can pass different columns if needed.

Here’s an example that uses two different columns for LAST_VALUE() and the ORDER BY clause:

SELECT 
    District, 
    Name, 
    Population AS "City Population", 
    LAST_VALUE( Name ) OVER win AS "Last Value"
FROM City 
WHERE CountryCode = 'AUS'
WINDOW win AS ( 
    PARTITION BY District 
    ORDER BY Population DESC
    ROWS UNBOUNDED PRECEDING
    );

Result:

+-----------------+---------------+-----------------+---------------+
| District        | Name          | City Population | Last Value    |
+-----------------+---------------+-----------------+---------------+
| Capital Region  | Canberra      |          322723 | Canberra      |
| New South Wales | Sydney        |         3276207 | Sydney        |
| New South Wales | Newcastle     |          270324 | Newcastle     |
| New South Wales | Central Coast |          227657 | Central Coast |
| New South Wales | Wollongong    |          219761 | Wollongong    |
| Queensland      | Brisbane      |         1291117 | Brisbane      |
| Queensland      | Gold Coast    |          311932 | Gold Coast    |
| Queensland      | Townsville    |          109914 | Townsville    |
| Queensland      | Cairns        |           92273 | Cairns        |
| South Australia | Adelaide      |          978100 | Adelaide      |
| Tasmania        | Hobart        |          126118 | Hobart        |
| Victoria        | Melbourne     |         2865329 | Melbourne     |
| Victoria        | Geelong       |          125382 | Geelong       |
| West Australia  | Perth         |         1096829 | Perth         |
+-----------------+---------------+-----------------+---------------+
14 rows in set (0.01 sec)

Here, we passed the Name column to the LAST_VALUE() function, but we sorted by the Population column (in descending order).

Syntax

Example

Partitioning

The null_treatment Clause

Changing the Order

Passing a Different Column to LAST_VALUE()

The `null_treatment` Clause

Passing a Different Column to `LAST_VALUE()`