SQL Reference - LAST

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

expression: This is the value to be returned as the last value in the ordered partition. It is a column or a formula within the function LAST_VALUE().
partition by partition_expression: It is used to divide the result set into partitions where function is applied separately for each. It is optional.
order by sort_expression [asc | desc]: This parameter is used to order the rows within each partition. It can be ascending (ASC) or descending (DESC).
frame_specification: Specifies the range of rows within a partition to be used for the calculation, such as “ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”. It is optional in some databases but required in others.

Example

CREATE TABLE example (
  id INT,
  some_value INT
);

INSERT INTO example (id, some_value)
VALUES
(1, 10),
(2, 20),
(3, 30);

SELECT
  id,
  LAST_VALUE(some_value) OVER (ORDER BY id) AS LastValue
FROM
  example

Output

id	LastValue
1	10
2	20
3	30

Explanation

In the SQL script provided, a table example with two columns id and some_value is created. Three records are inserted into this table. The LAST_VALUE function retrieves the last value in the ordered (by id) partition of some_value for each row in example. From the output, it’s clear the LastValue column shows the current maximum some_value encountered in the ordered set up to the current row.

LAST_VALUE( [scalar_expression ] ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

scalar_expression: This is the value that is evaluated for the resulting set, which could be a column, subselect, any valid select statement or an expression.
partition_by_clause: This clause divides the data set into partitions to which the LAST_VALUE function is applied. Essentially, it provides a way to create subsets of data.
order_by_clause: This clause is used to order the data in each partition. LAST_VALUE function works on the ordered set, returning last value depending on the order.
rows_range_clause: This defines the exact window frame within a partition row set you’re working with. This can specify unbounded preceding rows, defined number of preceding rows, current row, defined number of following rows, or unbounded following rows. The LAST_VALUE function is applied to this specific set.

Example

CREATE TABLE Example (
  ID INT,
  Person VARCHAR(100),
  value INT
);

INSERT INTO Example
VALUES (1, 'John', 50), (2, 'Peter', 20), (3, 'Sandra', 30);

SELECT
  ID,
  Person,
  value,
  LAST_VALUE(value) OVER (ORDER BY ID) as LastValue
FROM
  Example;

Output

| ID | Person | value | LastValue |
|----|--------|-------|-----------|
| 1  | John   | 50    | 50        |
| 2  | Peter  | 20    | 20        |
| 3  | Sandra | 30    | 30        |

Explanation

In this example, the LAST_VALUE function is used to fetch the last value in the value column for each row, when ordered by the ID. As such, the LastValue column displays the last value encountered in the value column for the current ‘window’ in the dataset.

LAST_VALUE(expression) IGNORE NULLS OVER ([query_partition_clause] [order_by_clause])

expression: This is the value to be returned, from the set of values being evaluated. It can be a column name or any Arithmetic, Constant, or Function expression.
ignore nulls: This optional keyword, if specified, prompts the function to bypass NULL values in the expression. By default, NULL values are considered.
over clause: This SQL clause is used to perform operations on a window of rows in the table. It provides a way to use data from other rows in the table’s calculation, without using a self join.
query_partition_clause: Inside the OVER() clause, this partitions the data into groups, based upon column values. This clause defines on which column data needs to be partitioned for separate calculations for each data group.
order_by_clause: Inside the OVER() clause, this orders the rows in each partition by the specified column values. This clause defines how data needs to be ordered before calculation.

Example

SELECT LAST_VALUE(salary) OVER (ORDER BY hire_date) as "Last Value"
FROM employees;

Output

Last Value
----------
5000

Explanation

This SQL query will return the last “salary” value in the ordered sequence of “hire_date”. The LAST_VALUE() function navigates through the rows of the result set and returns the last salary value according to the hire_date, in this case 5000.

LAST_VALUE(value) OVER ([PARTITION BY partition_expression] [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST} ]])

value: The data item or input expression whose final value is to be determined.
partition by partition_expression: Divides the query result set into partitions. The LAST_VALUE() function will operate on each partition separately and return a result for each partition.
order by sort_expression: Sorts data in each partition on which the LAST_VALUE() function operates. The sort_expression is an expression that determines the order of the data items.
asc | desc: Designates whether the sort order is ascending (ASC) or descending (DESC). If omitted, ASC is default.
nulls {first | last}: Determines where NULL values should be placed in the ordered sequence. NULLS FIRST specifies that all NULL values should be sorted at the beginning, and NULLS LAST specifies that all NULL values should be sorted at the end.

Example

CREATE TABLE ExampleTable (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(50),
    item_stock INT
);

INSERT INTO ExampleTable (item_name, item_stock)
VALUES ('Apple', 10), ('Banana', 15), ('Cherry', 20);

SELECT
  item_name,
  item_stock,
  LAST_VALUE(item_stock) OVER (ORDER BY item_stock ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_value
FROM
  ExampleTable;

Output

 item_name | item_stock | last_value
-----------+------------+-----------
 Apple     |     10     |     20
 Banana    |     15     |     20
 Cherry    |     20     |     20

Explanation

In the given example, the LAST_VALUE function is applied over the item_stock field in the ExampleTable. The function is executed on the full window frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). It results in the last value of each row when ordered by item_stock, which is consistently 20, the highest stock value.