Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Window ordered aggregate
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some aggregate functions already specify ordering for their aggregation. These include:
- ordered aggregate functions
- ordered set aggregate functions (subtle difference, see those sections)
In principle, there's nothing preventing users from ordering an ordered aggregate window function twice. While few dialects actually support this, here's how it would look:
SELECT
ID,
array_agg(ID ORDER BY ID DESC) OVER (),
array_agg(ID ORDER BY ID DESC)
OVER (ORDER BY ID)
FROM
BOOK
create.select(
BOOK.ID,
arrayAgg(BOOK.ID.desc()).orderBy(BOOK.ID).over(),
arrayAgg(BOOK.ID.desc()).orderBy(BOOK.ID)
.over(orderBy(BOOK.ID)))
.from(BOOK)
.fetch();
Producing:
+----+-----------+-----------+ | ID | ARRAY_AGG | ARRAY_AGG | +----+-----------+-----------+ | 1 | [4,3,2,1] | [1] | | 2 | [4,3,2,1] | [2,1] | | 3 | [4,3,2,1] | [3,2,1] | | 4 | [4,3,2,1] | [4,3,2,1] | +----+-----------+-----------+
While the aggregation itself lists the values in descending order, the window specification may or may not sort the values, meaning that if it does, the window frame only includes the preceding rows, whereas otherwise, all the rows are included.
Feedback
Do you have any feedback about this page? We'd love to hear it!