Available in versions: Dev (3.20)
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.
MAX_BY
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The MAX_BY() aggregate function calculates a value at the row containing the maximum value of all by values. This works the same way as the Oracle specific KEEP clause.
SELECT max_by(AUTHOR_ID, ID) FROM BOOK
create.select(maxBy(BOOK.AUTHOR_ID, BOOK.ID))
.from(BOOK)
Producing:
+--------+ | max_by | +--------+ | 2 | +--------+
Dialect support
This example using jOOQ:
maxBy(BOOK.AUTHOR_ID, BOOK.ID)
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, H2, Postgres, YugabyteDB
(array_agg(BOOK.AUTHOR_ID ORDER BY BOOK.ID DESC))[1]
BigQuery, DuckDB, Snowflake, Trino
max_by(BOOK.AUTHOR_ID, BOOK.ID)
ClickHouse
argMax(BOOK.AUTHOR_ID, BOOK.ID)
Hana
last_value(BOOK.AUTHOR_ID ORDER BY BOOK.ID)
HSQLDB
CASE WHEN cardinality(array_agg(BOOK.AUTHOR_ID ORDER BY BOOK.ID DESC)) >= 1 THEN (array_agg(BOOK.AUTHOR_ID ORDER BY BOOK.ID DESC))[1] END
Oracle
max(BOOK.AUTHOR_ID) KEEP (DENSE_RANK LAST ORDER BY BOOK.ID)
ASE, Access, Aurora MySQL, DB2, Derby, Exasol, Firebird, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLServer, SQLite, Sybase, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!