Select​

select–retrieve or compute a set of values.

[ with with-item [, ...] ]

select expr

[ filter filter-expr ]

[ order by order-expr [direction] [then ...] ]

[ offset offset-expr ]

[ limit  limit-expr ] ;

filter filter-expr

The optional filter clause, where filter-expr is any expression that has a result of type bool. The condition is evaluated for every element in the set produced by the select clause. The result of the evaluation of the filter clause is a set of boolean values. If at least one value in this set is true, the input element is included, otherwise it is eliminated from the output.

order by order-expr [direction] [then ...]

The optional order by clause has this general form:

order by
    order-expr [ asc | desc ] [ empty { first | last } ]
    [ then ... ]

The order by clause produces a result set sorted according to the specified expression or expressions, which are evaluated for every element of the input set.

If two elements are equal according to the leftmost expression, they are compared according to the next expression and so on. If two elements are equal according to all expressions, the resulting order is undefined.

Each expression can be an arbitrary expression that results in a value of an orderable type. Primitive types are orderable, object types are not. Additionally, the result of each expression must be an empty set or a singleton. Using an expression that may produce more elements is a compile-time error.

An optional asc or desc keyword can be added after any expression. If not specified asc is assumed by default.

If empty last is specified, then input values that produce an empty set when evaluating an expression are sorted after all other values; if empty first is specified, then they are sorted before all other values. If neither is specified, empty first is assumed when asc is specified or implied, and empty last when desc is specified.

offset offset-expr

The optional offset clause, where offset-expr is a singleton expression of an integer type. This expression is evaluated once and its result is used to skip the first element-count elements of the input set while producing the output. If element-count evaluates to an empty set, it is equivalent to offset 0, which is equivalent to omitting the offset clause. If element-count evaluates to a value that is larger then the cardinality of the input set, an empty set is produced as the result.

limit limit-expr

The optional limit clause, where limit-expr is a singleton expression of an integer type. This expression is evaluated once and its result is used to include only the first element-count elements of the input set while producing the output. If element-count evaluates to an empty set, it is equivalent to specifying no limit clause.

with module example

select clause
select
    expr  compute a set of things

optional clause
filter
    expr  filter the computed set

optional clause
order by
    expr  define ordering of the filtered set

optional clause
offset
    expr  slice the filtered/ordered set

optional clause
limit
    expr  slice the filtered/ordered set

with module example
select User {
    name,
    owned := (select
        User.<owner[is Issue] {
            number,
            body
        }
    )
}
filter User.name like 'Alice%';

with module example
select User {
    name,
    owned := (select
        User.<owner[is Issue] {
            number,
            body
        }
    )
}
filter User.name like 'Alice%'
limit 1;

with module example
select Issue {
    number,
    body,
    due_date
}
filter
    exists Issue.due_date
    and
    Issue.status.name = 'Open'
order by
    Issue.due_date
limit 3;

with module example
select count(User)
filter User.name like 'Alice%';

with module example
select _filter(
    count(User),
    User.name like 'Alice%'
);

# This will actually only count users whose name starts with
# 'Alice'.

with module example
select count(
    (select User
     filter User.name like 'Alice%')
);

# which can be represented as:
with module example
select count(
    _filter(User,
           User.name like 'Alice%')
);