Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
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.
ORDER BY
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The ORDER BY clause allows for ordering a window, which may be required by a window function to produce a deterministic result.
There are 2 reasons to order window functions:
- The window function requires it (e.g. ROW_NUMBER).
- The window has a explicit or implicit window frame.
While the window frame ordering will be discussed later, it is easy to see how ROW_NUMBER relies on ordering in an example:
SELECT BOOK.ID, row_number() OVER (ORDER BY id DESC) FROM BOOK
create.select(
BOOK.ID,
rowNumber().over(orderBy(BOOK.ID.desc())))
.from(BOOK)
.fetch();
Producing:
+----+------------+ | id | row_number | +----+------------+ | 1 | 4 | | 2 | 3 | | 3 | 2 | | 4 | 1 | +----+------------+
If you omit the ORDER BY clause (and if that's supported in a window function), then no frame clause is implied, and thus the entire window partition is being considered.
Dialect support
This example using jOOQ:
rowNumber().over(orderBy(BOOK.ID))
Translates to the following dialect specific expressions:
Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, Databricks, DuckDB, Exasol, Firebird, H2, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
row_number() OVER (ORDER BY BOOK.ID)
ASE, Access, Aurora MySQL, Derby, HSQLDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!