Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Ordering
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some aggregate functions allow for ordering their inputs to produce an ordered output. These aggregate functions allow for specifying an optional ORDER BY clause in their argument list. This is not to be confused with the WITHIN GROUP (ORDER BY ..) clause, which is required for ordering inputs to produce a single, unordered output.
This makes a lot of sense with aggregations that produce the aggregated values in a nested or formatted data structure, such as, for example:
- ARRAY_AGG, which aggregates data into an array.
- COLLECT, which aggregates data into a nested table (Oracle).
-
LISTAGG, which aggregates data into a string. The standard
LISTAGGfunction, unfortunately, inconsistently uses the WITHIN GROUP syntax. MySQL'sGROUP_CONCATis more consistent with the rest.
An example using ARRAY_AGG could look like this:
SELECT array_agg(ID), array_agg(ID ORDER BY ID DESC) FROM BOOK
create.select(
arrayAgg(BOOK.ID),
arrayAgg(BOOK.ID).orderBy(BOOK.ID.desc()))
.from(BOOK)
Producing:
+--------------+--------------+ | array_agg | array_agg | +--------------+--------------+ | [1, 3, 4, 2] | [4, 3, 2, 1] | +--------------+--------------+
Notice that in the absence of an explicit ORDER BY clause, as always, the ordering is non deterministic.
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!