JOOQ How to select the min 'id' from a table

Question

In mysql I want to execute a query like this

SELECT MIN(id) FROM table;

The more I read about JOOQ syntax and the aggregate functions, the confused I get.

I thought something like this would work

select( EVENT.EVENTID , min() ).from( EVENT ).fetch();
or
Result<Integer> er = context.select( EVENT.EVENTID.min()).fetch();

I tried a work around by selecting the whole first record

Result<EventRecord> er2 = context.selectFrom(EVENT).orderBy(EVENT.EVENTID.asc()).limit(1).fetch();

If the result has size 0, a record does not exist, but when it is not 0 I get the right record. I would like to use the min() function but can't get the syntax right.

Answer 1

The query you want to write in SQL is this one:

SELECT MIN(event.eventid) FROM event

This is why your two attempts didn't work

// 1. You cannot combine single columns with aggregate functions in SQL,
//    unless you're grouping by those columns
// 2. You didn't pass any cargument column to the MIN() function
context.select( EVENT.EVENTID , min() ).from( EVENT ).fetch();

// 3. This doesn't specify any FROM clause, so your database won't know what
//    table you want to select the MIN(eventid) from
context.select( EVENT.EVENTID.min()).fetch();

Note that these thoughts are not specific to jOOQ, they are related to SQL in general. When using jOOQ, always think of the SQL statement you want to express first (the one at the top of my answer). So your jOOQ statement would look like any of these:

// "Postfix notation" for MIN()
context.select(EVENT.EVENTID.min()).from(EVENT).fetch();

// "Prefix notation" for MIN(), where min() is static-imported from
// org.jooq.impl.DSL
context.select(min(EVENT.EVENTID)).from(EVENT).fetch();