|The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : Ordering||previous : next|
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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).
- JSON_ARRAYAGG, which aggregates data into a JSON array.
LISTAGG, which aggregates data into a string. The standard
LISTAGGfunction, unfortunately, inconsistently uses the WITHIN GROUP syntax. MySQL's
GROUP_CONCATis more consistent with the rest.
- XMLAGG, which aggregates data into an XML element.
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)
+--------------+--------------+ | 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.