Using FIRST_VALUE in GBase 8a for OLAP Analysis

FIRST_VALUE is a window function in GBase 8a that returns the first value in an ordered window. It works like ROW_NUMBER = 1 but returns the actual column value instead of the row number. This article explains the syntax and demonstrates common usage patterns.

Syntax

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

• expr: A column or expression whose value from the first row is returned.
• ORDER BY defines which row is "first". Omitting it makes the result non‑deterministic (natural storage order).
• 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. The function returns the val from the row with the smallest id in each partition.

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

Output:

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

When ordering by id DESC, the first row in each partition is the one with the largest id, which may be NULL.

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

Without PARTITION BY

The whole table is one window. Ordering by id ASC makes the first value 111.000.

SELECT *, FIRST_VALUE(val) OVER (ORDER BY id) AS first_val FROM td;

Ordering by id DESC yields NULL as the first value.

SELECT *, FIRST_VALUE(val) OVER (ORDER BY id DESC) AS first_val FROM td;

With PARTITION BY but Without ORDER BY

Each partition returns the first value in natural storage order. Results are unpredictable — avoid this in production queries.

SELECT *, FIRST_VALUE(val) OVER (PARTITION BY type) AS first_val FROM td;

Without PARTITION BY or ORDER BY

All rows share the same first value from the natural order of the table.

SELECT *, FIRST_VALUE(val) OVER () AS first_val FROM td;

FIRST_VALUE is a fundamental window function in a gbase database for OLAP workloads. It helps you retrieve the earliest, smallest, or most important row in a group — without writing complex self‑joins. Combine it with LAST_VALUE to capture both ends of a partition in a single query.