CODEWITHSUNDEEP
javasqljooq
Mark
Mark

Reputation: 161

How do you create a multi-column subquery in jooq

I am trying to make a JOOQ query that mimics the following SQL:

select label,orderdate,price 
from orders 
where (label,orderdate) in (select label,max(orderdate) from orders group by label);

Is there a way to do this in JOOQ?

Upvotes: 0

Views: 866

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221210

Use the DSL.row() constructor.

DSL.using(configuration)
   .select(ORDERS.LABEL, ORDERS.ORDERDATE, ORDERS.PRIC)
   .from(ORDERS)
   .where(row(ORDERS.LABEL, ORDERS.ORDERDATE)
       .in(select(ORDERS.LABEL, max(ORDERS.ORDERDATE))
            .from(ORDERS)
            .groupBy(ORDERS.LABEL)))
   .fetch();

More info here: https://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/in-predicate-degree-n

Alternative using window functions

Note, though, if your database supports window functions, chances are that this alternative query might be faster:

select label, orderdate, price
from (
  select 
    label, orderdate, price, 
    rank() over (partition by label order by orderdate desc) rk
  from orders
) orders
where rk = 1

This would also be supported by jOOQ

Oracle alternative

If you're using Oracle, the fastest solution would be using the FIRST function (not exactly equivalent: if you have several rows per orderdate, you'll get only one):

select 
  label, 
  MAX(orderdate) KEEP (DENSE_RANK FIRST ORDER BY orderdate DESC), 
  MAX(price)     KEEP (DENSE_RANK FIRST ORDER BY orderdate DESC)
orders
group by label

This is also supported by jOOQ.

More information on these techniques in this article about efficient TOP N queries in SQL

Upvotes: 2

Related Questions