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 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 | +-----------------+
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!