Search code examples
javamysqljooq

jOOQ Subquery in Order By

I'm using a subquery in order by like this on MySQL 8 database:

select * from series 
order by (select max(competition.competition_date) from competition 
          where competition.series_id = series.id) desc

But I didn't find a way to do that with jOOQ.

I tried the following query but this does not compile:

dsl
   .selectFrom(SERIES)
   .orderBy(dsl.select(DSL.max(COMPETITION.COMPETITION_DATE))
               .from(COMPETITION).where(COMPETITION.SERIES_ID.eq(SERIES.ID)).desc())
   .fetch()

Are subqueries not supported in order by?

Solution

  • Turning the subquery into a Field works:

    dsl.selectFrom(SERIES)
   .orderBy(DSL.field(dsl.select(DSL.max(COMPETITION.COMPETITION_DATE)).from(COMPETITION)
                  .where(COMPETITION.SERIES_ID.eq(SERIES.ID))).desc())
   .fetch()