jooq query for sql query with analytical function

Question

i have sql query like

select *
from (
   select s.*, max(year) over () max_year
   from Transaction s
   where s.Account_Number     = '4234242234'
     and s.month in (0,1)) o
where o.year=o.max_year;

i need to write in jOOQ, any help. I tried the below

dslContext.select(Transaction.BANK,
                    Transaction.ACCOUNT,
                    Transaction.AMOUNT,
                    max(Transaction.YEAR).over())
            .from(Transaction)
            .where(Transaction.BANK.eq(bank))
            .and(Transaction.MONTH.in((byte) 0, (byte) 1))
            .fetchInto(Transaction.class);

Also,is there any way to avoid listing all columns in select. Instead of

select(Transaction.BANK,
       Transaction.ACCOUNT,
       Transaction.AMOUNT,
       max(Transaction.YEAR).over())

Something like

select(Transaction.*,
       max(Transaction.YEAR).over())

Answer 1

A derived table can be constructed like this:

// Assuming this static import, as always:
import static org.jooq.impl.DSL.*;

// Create aliased column expression for later reuse
Field<Integer> maxYear = max(Transaction.YEAR).over().as("max_year");

// Create derived table
Table<?> o = select(Transaction.asterisk(), maxYear)
            .from(Transaction)
            .where(Transaction.BANK.eq(bank))
            .and(Transaction.MONTH.in((byte) 0, (byte) 1))
            .asTable("o");

// Use derived table and dereference fields from it using Table.field(Field)
ctx.select()
   .from(o)
   .where(o.field(Transaction.YEAR).eq(o.field(maxYear)))
   .fetch();

Also, is there any way to avoid listing all columns in select

You're looking for Table.asterisk() to produce the asterisk in SQL, or Table.fields() to produce all fields from your generated code.