New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6

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).
  • JSON_ARRAYAGG, which aggregates data into a JSON array.
  • LISTAGG, which aggregates data into a string. The standard LISTAGG function, unfortunately, inconsistently uses the WITHIN GROUP syntax. MySQL's GROUP_CONCAT is 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)

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.

The jOOQ Logo