How the LAST_VALUE() function works in Mariadb?

The LAST_VALUE() function is a window function that returns the last value of an expression within a partition or a result set. It can be used to get the most recent or the latest value of a column or an expression, or to compare the current value with the previous value. In this article, we will learn how the LAST_VALUE() function works in Mariadb, what are its syntax and parameters, and how to use it with some examples. We will also explore some related functions that can be used with the LAST_VALUE() function.

Syntax

The syntax of the LAST_VALUE() function is as follows:

LAST_VALUE(expr) OVER (
  [PARTITION BY partition_expression]
  [ORDER BY order_expression [ASC|DESC]]
  [frame_clause]
)

The function takes one required argument and three optional clauses:

• expr: The expression to return the last value of. It can be any valid expression that can be used in a SELECT statement.

• PARTITION BY partition_expression: The clause that divides the result set into partitions based on the values of the partition expression. The function returns the last value of the expression within each partition. If omitted, the function treats the whole result set as a single partition.

• ORDER BY order_expression [ASC|DESC]: The clause that orders the rows within each partition based on the values of the order expression. The function returns the last value of the expression based on the order of the rows. The order can be ascending (ASC) or descending (DESC). If omitted, the function returns an arbitrary value from each partition.

• frame_clause: The clause that specifies the range of rows to consider for the function. It can be one of the following forms:

  • ROWS BETWEEN start AND end: The function considers the rows between the start and the end of the frame, where start and end can be one of the following values:

    • UNBOUNDED PRECEDING: The first row of the partition.
    • UNBOUNDED FOLLOWING: The last row of the partition.
    • CURRENT ROW: The current row of the partition.
    • N PRECEDING: The Nth row before the current row of the partition, where N is a positive integer.
    • N FOLLOWING: The Nth row after the current row of the partition, where N is a positive integer.

  • RANGE BETWEEN start AND end: The function considers the rows that have the same value as the current row for the order expression, plus the rows between the start and the end of the frame, where start and end can be one of the following values:

    • UNBOUNDED PRECEDING: The first row of the partition.
    • UNBOUNDED FOLLOWING: The last row of the partition.
    • CURRENT ROW: The current row of the partition.
    • N PRECEDING: The Nth value before the current value of the order expression, where N is a positive integer.
    • N FOLLOWING: The Nth value after the current value of the order expression, where N is a positive integer.

If the frame_clause is omitted, the function uses the default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The function returns the same data type as the expression. If the expression is NULL, the function returns NULL. If the expression is invalid, the function returns an error.

Examples

Let’s see some examples of how to use the LAST_VALUE() function in Mariadb.

Example 1: Using the function without the PARTITION BY and ORDER BY clauses

In this example, we will use the LAST_VALUE() function without the PARTITION BY and ORDER BY clauses. We will create a table named sales with some sample data, and use the LAST_VALUE() function to get the last value of the amount column.

CREATE TABLE sales (
  id INT PRIMARY KEY,
  date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO sales VALUES
(1, '2024-01-01', 100.00),
(2, '2024-01-02', 200.00),
(3, '2024-01-03', 300.00),
(4, '2024-01-04', 400.00),
(5, '2024-01-05', 500.00);

SELECT id, date, amount, LAST_VALUE(amount) OVER () AS last_amount FROM sales;

id | date       | amount | last_amount
---|------------|--------|------------
1  | 2024-01-01 | 100.00 | 500.00
2  | 2024-01-02 | 200.00 | 500.00
3  | 2024-01-03 | 300.00 | 500.00
4  | 2024-01-04 | 400.00 | 500.00
5  | 2024-01-05 | 500.00 | 500.00

The function returns the value 500.00 for each row, which is the last value of the amount column in the whole result set.

Example 2: Using the function with the PARTITION BY clause

In this example, we will use the LAST_VALUE() function with the PARTITION BY clause. We will use the same table as the previous example, and use the LAST_VALUE() function to get the last value of the amount column within each month.

SELECT id, date, amount, LAST_VALUE(amount) OVER (PARTITION BY MONTH(date)) AS last_amount FROM sales;

id | date       | amount | last_amount
---|------------|--------|------------
1  | 2024-01-01 | 100.00 | 500.00
2  | 2024-01-02 | 200.00 | 500.00
3  | 2024-01-03 | 300.00 | 500.00
4  | 2024-01-04 | 400.00 | 500.00
5  | 2024-01-05 | 500.00 | 500.00

The function returns the value 500.00 for each row, which is the last value of the amount column in the same month as the current row.

Example 3: Using the function with the ORDER BY clause

In this example, we will use the LAST_VALUE() function with the ORDER BY clause. We will use the same table as the previous examples, and use the LAST_VALUE() function to get the last value of the amount column based on the order of the date column.

SELECT id, date, amount, LAST_VALUE(amount) OVER (ORDER BY date) AS last_amount FROM sales;

id | date       | amount | last_amount
---|------------|--------|------------
1  | 2024-01-01 | 100.00 | 100.00
2  | 2024-01-02 | 200.00 | 200.00
3  | 2024-01-03 | 300.00 | 300.00
4  | 2024-01-04 | 400.00 | 400.00
5  | 2024-01-05 | 500.00 | 500.00

The function returns the same value as the amount column for each row, which is the last value of the amount column based on the order of the date column.

Example 4: Using the function with the frame_clause

In this example, we will use the LAST_VALUE() function with the frame_clause. We will use the same table as the previous examples, and use the LAST_VALUE() function to get the last value of the amount column within a frame of three rows before the current row.

SELECT id, date, amount, LAST_VALUE(amount) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS last_amount FROM sales;

id | date       | amount | last_amount
---|------------|--------|------------
1  | 2024-01-01 | 100.00 | 100.00
2  | 2024-01-02 | 200.00 | 200.00
3  | 2024-01-03 | 300.00 | 300.00
4  | 2024-01-04 | 400.00 | 400.00
5  | 2024-01-05 | 500.00 | 500.00

The function returns the same value as the amount column for each row, which is the last value of the amount column within a frame of three rows before the current row.

Conclusion

In this article, we learned how the LAST_VALUE() function works in Mariadb, what are its syntax and parameters, and how to use it with some examples. We also explored some related functions that can be used with the LAST_VALUE() function. The LAST_VALUE() function is a useful tool for getting the last value of an expression within a partition or a result set, or for comparing the current value with the last value.