order_by

Description

Specify the ordering when processing data. This is used by ordered windowing functions (like lag and last) as well as ordered aggregation functions (like array_agg).

Ordering is specified by alternating fields and a literal boolean for whether the field is in ascending (false) or descending (true) order. Because of backwards compatibility, the function will default to ascending if you omit specifying a boolean value, however, doing so makes for sometimes ambiguous expressions, and is not recommended.

Return type

ordering

Domain

This is a scalar function (calculates a single output value for a single input row.)

Categories

Usage

order_by(columnname_1, [ descending_1 ], columnname_2, [ descending_2 ], ...)

Argument

Type

Optional

Repeatable

Restrictions

columnname

storable

no

yes

column

descending

bool

yes

yes

constant

order_by(ordering_1, ordering_2, ...)

Argument

Type

Optional

Repeatable

Restrictions

ordering

ordering

no

yes

constant

Examples

statsby list:array_agg(username, order_by(timestamp, false)), group_by(hostname)

This produces a list of all users for each host, in order of timestamp within each host, from a table with columns timestamp, hostname and username.

statsby list:array_agg(make_object(time:timestamp, host:hostname), order_by(hostname, false, timestamp, true)), group_by(username)

This produces a list of all times and hosts a user has been on, ordered by host alphabetically and reverse chronologically, from a table with columns timestamp, hostname and username.

Aliases

orderby (deprecated)