The jOOQ User Manual SQL building Column expressions Aggregate functions Ordering WITHIN GROUP

previous : next

Ordering WITHIN GROUP

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 a mandatory WITHIN GROUP (ORDER BY ..) clause after the function. This is not to be confused with the aggregate ORDER BY clause, which allows for optionally ordering inputs to produce ordered output

Standard SQL talks about "ordered set aggregate functions" which come in three flavours

• Hypothetical set functions: Functions that check for the position of a hypothetical value inside of an ordered set. These include RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST.
• Inverse distribution functions: Functions calculating a percentile over an ordered set, including PERCENTILE_CONT, PERCENTILE_DISC, or MODE.
• LISTAGG, which is inconsistently using the WITHIN GROUP syntax, as it is used to order the output of the function, and isn't mandatory in all dialects.

An example for the PERCENTILE_CONT inverse distribution function is this:

SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY ID)
FROM BOOK

create.select(
         percentileCont(0.5).withinGroupOrderBy(BOOK.ID))
      .from(BOOK)

Producing the median BOOK.ID value:

+-----------------+
| percentile_cont |
+-----------------+
|             2.5 |
+-----------------+

The jOOQ User Manual SQL building Column expressions Aggregate functions Ordering WITHIN GROUP

previous : next