9.2 Ordering, paging and sampling

sort

Definition. Orders rows by one or more keys.

Syntax

sort <key> [asc | desc] [, <key> [asc | desc]]*
key := <field.path> | <expression>

Behavior

• Stable: rows with equal keys keep their input order.
• The default direction is asc.
• A key may be any expression (sort price * qty desc, sort len(name), name): it is computed into a hidden field, sorted on, then dropped.
• Missing fields and null come first ascending, last descending. Different kinds sort by kind: booleans, numbers, strings, arrays, objects.
• Blocking: it needs every input row before emitting one; it spills sorted runs to disk past its memory.
• sort … | limit N (or | first, or | skip k | limit n) runs as a bounded Top-N holding only N rows.
• One sort per pipeline.

Examples

on orders | sort price desc | limit 2
on orders | sort price * qty desc | limit 3
on users  | select name, city | sort city, name
on orders | sort month desc, region

Errors and limits

• sort operator is duplicated.
• A number and a numeric text are not compared by value: convert first (derive n = to_float(code) | sort n).

See also limit, window, sort_array.

limit

Definition. Keeps the first n rows.

Syntax

limit <non-negative integer>

Behavior. Declares a row bound: the scan stops as soon as n rows have passed. After a blocking stage it bounds that stage (Top-N). One per pipeline; a parameter works (limit $n).

Examples

on orders | limit 10
on orders | sort qty desc | limit 3
on orders | skip 2 | limit 2 | select order_id

Errors. A negative or non-integer count is a planning error; limit operator is duplicated.

skip

Alias: offset

Definition. Drops the first n rows.

Syntax

skip <non-negative integer>

Examples

on orders | skip 2 | limit 2
on orders | offset 4
on orders | sort order_id | skip 10 | limit 5      -- a bounded Top-15, then the page

first

Definition. Keeps the first row that reaches it.

Syntax

first [<field.path>]

Behavior

• Declares a row bound of 1, like limit 1: the scan stops at the first row.
• Works inside lookup and union sub-pipelines (the first match per outer row).
• Without a path it is a pure bound, so sort … | first is a Top-1. With a path it also projects that field.
• Unlike limit, it may appear several times.

Examples

on users | first | select name
on users | first profile
on orders | select order_id, price | sort price desc | first
on users | lookup orders on user == name into o | …             -- every match
on users as u | lookup orders as o | where o.user == u.name | sort month desc | first | into last_order | end

See also single, first measure of group.

single

Definition. Lets exactly one row through, or fails.

Syntax

single [<field.path>]

Behavior. The first row passes; a second row fails the query, and so does the end of input with none. With a path it also projects.

Examples

on users | where name == "Alice" | single | select name, age
-- {"age":31,"name":"Alice"}
on users | where city == "Paris" | single
-- error: single expected exactly 1 row(s), found more
on users | where age > 100 | single
-- error: single expected exactly 1 row(s), found 0

Limits. When streaming, the first row may have been sent before a second one fails the query; the response still ends in error.

sample

Definition. Keeps n rows drawn uniformly at random (all of them when there are fewer).

Syntax

sample <n>

Behavior. A reservoir sample (algorithm R): memory bounded by n whatever the input size. Not deterministic, so a lookup holding sample is never shared between outer rows.

Examples

on orders | where status == "paid" | sample 100
on orders | select order_id | sample 3 | limit 2