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.
MIN_BY
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The MIN_BY() aggregate function calculates a value at the row containing the minimum value of all by values. This works the same way as the Oracle specific KEEP clause.
SELECT min_by(AUTHOR_ID, ID) FROM BOOK
create.select(minBy(BOOK.AUTHOR_ID, BOOK.ID))
.from(BOOK)
Producing:
+--------+ | min_by | +--------+ | 1 | +--------+
Dialect support
This example using jOOQ:
minBy(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))[1]
BigQuery, DuckDB, Snowflake, Trino
min_by(BOOK.AUTHOR_ID, BOOK.ID)
ClickHouse
argMin(BOOK.AUTHOR_ID, BOOK.ID)
Hana
first_value(BOOK.AUTHOR_ID ORDER BY BOOK.ID)
HSQLDB
CASE WHEN cardinality(array_agg(BOOK.AUTHOR_ID ORDER BY BOOK.ID)) >= 1 THEN (array_agg(BOOK.AUTHOR_ID ORDER BY BOOK.ID))[1] END
Oracle
min(BOOK.AUTHOR_ID) KEEP (DENSE_RANK FIRST 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!