1

My requirement is to take a list of identifiers, each of which could refer to multiple records, and return the newest record per identifier.

This would seem to be doable with a combination of orderBy(date, desc) and fetchGroups() on the identifier column. I then use values() to get the Result objects.

At this point, I want the first value in each result object. I can do get(0) to get the first value in the list, but that seems like cheating. Is there a better way to get that first result from a Result object?

JasonB
  • 302
  • 1
  • 3
  • 13

1 Answers1

3

You're going to write a top-1-per-category query, which is a special case of a top-n-per-category query. Most syntaxes that produce this behaviour in SQL are supported by jOOQ as well. You shouldn't use grouping in the client, because you'd transfer all the excess data from the server to the client, which corresponds to the remaining results per group.

Some examples:

Standard SQL (when window functions are supported)

Field<Integer> rn = rowNumber().over(T.DATE.desc()).as("rn");

var subquery = table(
    select(T.fields())
   .select(rn)
   .from(T)
).as("subquery");

var results = 
ctx.select(subquery.fields(T.fields())
   .from(subquery)
   .where(subquery.field(rn).eq(1))
   .fetch();

Teradata and H2 (we might emulate this soon)

var results = 
ctx.select(T.fields())
   .from(T)
   .qualify(rowNumber().over(T.DATE.desc()).eq(1))
   .fetch();

PostgreSQL

var results = 
ctx.select(T.fields())
   .distinctOn(T.DATE)
   .from(T)
   .orderBy(T.DATE.desc())
   .fetch();

Oracle

var results = 
ctx.select(
      T.DATE, 
      max(T.COL1).keepDenseRankFirstOrderBy(T.DATE.desc()).as(T.COL1),
      max(T.COL2).keepDenseRankFirstOrderBy(T.DATE.desc()).as(T.COL2),
      ...
      max(T.COLN).keepDenseRankFirstOrderBy(T.DATE.desc()).as(T.COLN))
   .from(T)
   .groupBy(T.DATE)
   .fetch();
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Let's say that for any given group, there are only 2-3 possible entries. Do you still think the window query is superior, or is the fetchGroups() approach easier to understand at that point? – JasonB May 07 '20 at 16:41
  • 1
    @JasonB: You're asking *me* if I would prefer the SQL approach? :-) – Lukas Eder May 07 '20 at 17:08
  • Thanks for all you do, Lukas! :-) – JasonB May 07 '20 at 17:10
  • 1
    `QUALIFY` deserves a greater adoption by RDBMS :) – Lukasz Szozda May 07 '20 at 18:03
  • @LukaszSzozda: Totally agreed! We'll support emulating it eventually in jOOQ, but need some internal infrastructure to support this, first. – Lukas Eder May 07 '20 at 18:23
  • also relevant: https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by and https://stackoverflow.com/questions/9796078/selecting-rows-ordered-by-some-column-and-distinct-on-another – JasonB May 09 '20 at 00:55