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:

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!

The jOOQ Logo