Using LAST_VALUE in GBase 8a for OLAP Analysis

LAST_VALUE is a window function in GBase 8a that returns the last value in an ordered window, evaluated up to the current row. It complements FIRST_VALUE and is essential for tracking the most recent value within a partition. This article explains the syntax and demonstrates common usage patterns.

Syntax

LAST_VALUE(expr) OVER (
    [PARTITION BY col1, col2, ...]
    ORDER BY col1 [ASC|DESC], col2 [ASC|DESC], ...
)

• expr: A column or expression whose value from the last row (up to the current row) is returned.
• ORDER BY defines the window ordering. As you traverse rows, LAST_VALUE updates to reflect the latest value seen so far.
• PARTITION BY splits rows into groups; when omitted, the entire result set is a single window.

Sample Data

CREATE TABLE td(id INT, type INT, val DECIMAL(20,3));
INSERT INTO td VALUES
    (1,1,111), (1,1,222), (2,1,333), (2,2,444),
    (3,2,555), (3,2,666), (4,2,777),
    (5,1,NULL), (50,2,NULL);

Usage Examples

Full Window (PARTITION BY + ORDER BY)

Partition by type and order by id ascending. As the scan moves down each partition, last_val updates to the latest val seen so far.

SELECT *, LAST_VALUE(val) OVER (PARTITION BY type ORDER BY id) AS last_val
FROM td;

Output:

id	type	val	last_val
1	1	111.000	111.000
1	1	222.000	222.000
2	1	333.000	333.000
5	1	NULL	NULL
2	2	444.000	444.000
3	2	555.000	555.000
3	2	666.000	666.000
4	2	777.000	777.000
50	2	NULL	NULL

When ordering by id DESC, the last value in each descending sequence is returned.

SELECT *, LAST_VALUE(val) OVER (PARTITION BY type ORDER BY id DESC) AS last_val
FROM td;

Without PARTITION BY

The whole table is one window, ordered by id ASC. last_val progresses through the values and finally reaches NULL.

SELECT *, LAST_VALUE(val) OVER (ORDER BY id) AS last_val FROM td;

Ordering by id DESC yields a different progression of last values.

SELECT *, LAST_VALUE(val) OVER (ORDER BY id DESC) AS last_val FROM td;

With PARTITION BY but Without ORDER BY

Each partition returns the last value in natural storage order. Results are non‑deterministic — avoid in production queries.

SELECT *, LAST_VALUE(val) OVER (PARTITION BY type) AS last_val FROM td;

Without PARTITION BY or ORDER BY

All rows return the last value in the table's natural order.

SELECT *, LAST_VALUE(val) OVER () AS last_val FROM td;

LAST_VALUE is frequently paired with FIRST_VALUE in a gbase database to capture both the earliest and latest records in a partition — ideal for time‑series analysis, change detection, and range calculations in OLAP workloads.