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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

PARTITION BY

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Just like the GROUP BY clause, the PARTITION BY clause divides the data set into mutually exclusive groups / partitions.

This is best illustrated by example, where we fetch all the books and the total number of books per author of a book:

SELECT
  BOOK.ID,
  BOOK.AUTHOR_ID,
  count(*) OVER (PARTITION BY BOOK.AUTHOR_ID)
FROM
  BOOK
create.select(
         BOOK.ID,
         BOOK.AUTHOR_ID,
         count().over(partitionBy(BOOK.AUTHOR_ID)))
      .from(BOOK)
      .fetch();

Producing:

+----+-----------+-------+
| id | author_id | count |
+----+-----------+-------+
|  1 |         1 |     2 |
|  2 |         1 |     2 |
|  3 |         2 |     2 |
|  4 |         2 |     2 |
+----+-----------+-------+

Just like ordinary aggregate functions that aggregate all data per GROUP produced by the GROUP BY clause, the PARTITION BY clause allows for grouping data, but without transforming the entire result set. As such, this kind of grouping can be much more convenient, especially in reporting, as the original data set isn't modified, only a new column is added.

If you omit the PARTITION BY clause, then the entire data set forms a single partition.
count().over(partitionBy(BOOK.AUTHOR_ID))

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo