SQLite LAST_VALUE function

The SQLite LAST_VALUE() function is a window function that returns the last value in a result set. This function is used in conjunction with the OVER clause, which defines a window or a subset of the result set.

Syntax

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

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

The expression parameter is the column or expression for which the last value should be returned. The PARTITION BY clause is optional and allows you to partition the result set into groups based on one or more columns. The ORDER BY clause is also optional and specifies the order in which the rows should be processed. The frame_clause parameter is also optional and specifies the range of rows to include in the window.

Example

Here is an example of how to use the LAST_VALUE() function in SQLite:

Suppose you have a table named “sales” with the following data:

id | date       | amount
---+------------+------
1  | 2022-01-01 | 100
2  | 2022-01-02 | 150
3  | 2022-01-03 | 200
4  | 2022-01-04 | 250
5  | 2022-01-05 | 300
6  | 2022-01-01 | 200
7  | 2022-01-02 | 250
8  | 2022-01-03 | 100
9  | 2022-01-04 | 150
10  | 2022-01-05 | 300

CREATE TABLE sales (
id INTEGER PRIMARY KEY,
date date,
amount INTEGER 
);
insert into sales(id, date, amount) values
(1, '2022-01-01', 100), (2, '2022-01-02', 150),
(3, '2022-01-03', 200), (4, '2022-01-04', 250),
(5, '2022-01-05', 300), (6, '2022-01-01', 200), 
(7, '2022-01-02', 250), (8, '2022-01-03', 100),
(9, '2022-01-04', 150), (10, '2022-01-05', 300);

You can use the LAST_VALUE() function to find the last amount value in the table as follows:

SELECT id, date, amount, 
LAST_VALUE(amount) OVER (PARTITION BY date ORDER BY amount
RANGE BETWEEN
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as last_amount
FROM sales;

This will produce the following result:

id | date       | amount| last_amount
---+------------+-------+-----------
1  | 2022-01-01 | 100   | 200
6  | 2022-01-01 | 200   | 200
2  | 2022-01-02 | 150   | 300
7  | 2022-01-02 | 250   | 250
8  | 2022-01-03 | 100   | 200
3  | 2022-01-03 | 200   | 200
9  | 2022-01-04 | 150   | 250
4  | 2022-01-04 | 250   | 250
5  | 2022-01-05 | 300   | 300
10  | 2022-01-05 | 300   | 300

As you can see, the LAST_VALUE() function returns the last value of amount within the specified window frame for each row in the query result.