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

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.

Dialect support

This example using jOOQ:

count().over(partitionBy(BOOK.AUTHOR_ID))

Translates to the following dialect specific expressions:

-- AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DB2, DUCKDB, EXASOL, FIREBIRD, H2, HANA, INFORMIX, MARIADB, MEMSQL, MYSQL, 
-- ORACLE, POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, YUGABYTEDB
count(*) OVER (PARTITION BY BOOK.AUTHOR_ID)

-- ACCESS, ASE, AURORA_MYSQL, DERBY, HSQLDB
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

References to this page

Feedback

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

The jOOQ Logo