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)