anyLast

Selects the last encountered value of a column.

Note

As a query can be executed in arbitrary order, the result of this function is non-deterministic. If you need an arbitrary but deterministic result, use functions min or max.

By default, the function never returns NULL, i.e. ignores NULL values in the input column. However, if the function is used with the RESPECT NULLS modifier, it returns the first value reads no matter if NULL or not.

Syntax

anyLast(column) [RESPECT NULLS]

Alias anyLast(column) (without RESPECT NULLS)

last_value.

Aliases for anyLast(column) RESPECT NULLS

anyLastRespectNulls, anyLast_respect_nulls
lastValueRespectNulls, last_value_respect_nulls

Parameters

column: The column name.

Returned value

The last value encountered.

Example

Query:

CREATE TABLE tab (city Nullable(String)) ENGINE=Memory;

INSERT INTO tab (city) VALUES ('Amsterdam'),(NULL),('New York'),('Tokyo'),('Valencia'),(NULL);

SELECT anyLast(city), anyLastRespectNulls(city) FROM tab;

┌─anyLast(city)─┬─anyLastRespectNulls(city)─┐
│ Valencia      │ ᴺᵁᴸᴸ                      │
└───────────────┴───────────────────────────┘

anyLast

Introduced in: v1.1

Selects the last encountered value of a column.

Note

As a query can be executed in arbitrary order, the result of this function is non-deterministic. If you need an arbitrary but deterministic result, use functions min or max.

By default, the function never returns NULL, i.e. ignores NULL values in the input column. However, if the function is used with the RESPECT NULLS modifier, it returns the last value reads no matter if NULL or not.

Syntax

anyLast(column) [RESPECT NULLS]

Aliases: last_value

Arguments

column — The column name. Any

Returned value

Returns the last value encountered. Any

Examples

Usage example

CREATE TABLE tab(city Nullable(String)) ENGINE=Memory;
INSERT INTO tab (city) VALUES ('Amsterdam'), (NULL), ('New York'), ('Tokyo'), ('Valencia'), (NULL);
SELECT anyLast(city), anyLastRespectNulls(city) FROM tab;

┌─anyLast(city)─┬─anyLastRespectNulls(city)─┐
│ Valencia      │ ᴺᵁᴸᴸ                      │
└───────────────┴───────────────────────────┘

anyLast​

anyLast