Aggregate functions and ORDER BY

Certain aggregate functions output significantly different results depending on their input order. By default, this ordering is not specified, but it can be controlled via an optional ORDER BY clause as an argument. So, in the presence of ORDER BY on these aggregate function calls, we can fetch ordered aggregated results. Let's see how we can use such functions in jOOQ and start with a category of functions having their names suffixed with AGG, such as ARRAY_AGG(), JSON_ARRAYAGG(), XML_AGG(), MULTISET_AGG() (covered in Chapter 8, Fetching and Mapping), and so on.

FOO_AGG()

For instance, ARRAY_AGG() is a function that aggregates data into an array and, in the presence of ORDER BY, it aggregates data into an array conforming to the specified order. Here is an example of using ARRAY_AGG() to aggregate EMPLOYEE.FIRST_NAME in descending order by EMPLOYEE.FIRST_NAME and LAST_NAME:

ctx.select(arrayAgg(EMPLOYEE.FIRST_NAME).orderBy(
  ...