CODEWITHSUNDEEP
mysqlsqljooq
LearnerForLife
LearnerForLife

Reputation: 157

JOOQ code to select rows which has latest timestamp from table

I am new to JOOQ. I have a use case where I need to select the rows which have the latest timestamp field value from a table.

My table is partitioned by event date. So when I query to table, I get multiple records for the same id. Now I do not want to get many records for same id, instead I need only one record to be returned which has latest timestamp field value.

Using basic SQL syntax I am able to achieve this, but I'm not able to achieve it through JOOQ.

The SQL:

select * 
from abc.student t 
inner join (
  select id, max(event_date) as MaxDate 
  from abc.student group by id 
) tm on t.id = tm.id and t.event_date = tm.MaxDate ;

The Java:

public Iterable<MyClass> fetchMyRecords(Iterable<String> MyIds) {
    return dslContext.selectFrom(MYTABLE)
    WHERE(MYTABLE.ID.in(List.newArrayList(ids)))
    .fetchInto(MyObject.class);
}

Expected results: How do I get a List<MyClass> which has distinct MyClass objects and each distinct object is the record which has latest timestamp value? My table has a field called timestamp, based on that I need to achieve this.

Update Question

Lets say i have to fetch list of students who has latest event_date from all the partitions . Below is the Jooq query which i have written ,but it is returning only one object of type MyStudent .

Question -- How do i apply limits for particular id . Given an list of n ids, i need to get n Mystudent objects where each object has max event_date

Below is the query which only returns 1 object

I have a another question , how do i do this without join . Below is the Jooq Code which is working for only one id . But my function accepts list of ids and get the records from the table.

public Iterable<MyClass> fetchMyRecords(Iterable<String> MyIds) {
return dslContext.select().from(TABLE)
.where(TABLE.ID.in(Lists.newArrayList(ids)))
.orderBy(TABLE.EVENT_DATE.desc())
.limit(1)
.fetchInto(MyStudent.class); }

Therefore given set of 3 ids (for example) , how do i fetch 3 Mystudent class object , where each object is max of (event_date) record. If i use limit, it will only return the first id record . If i do not use limit, it will return all the duplicate rows which are old for ID.

Upvotes: 4

Views: 3545

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

I'm translating the query from your comments to jOOQ:

// Assuming these static imports
import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;

Student t = STUDENT.as("t");
Field<Date> maxDate = max(STUDENT.EVENT_DATE).as("MaxDate");
Table<?> tm = table(select(STUDENT.ID, maxDate)
                   .from(STUDENT)
                   .groupBy(STUDENT.ID)).as("tm");

ctx.select()
   .from(t)
   .join(tm)
   .on(t.ID.eq(tm.field(STUDENT.ID)))
   .and(t.EVENT_DATE.eq(tm.field(maxDate)))
   .fetch();

Upvotes: 3

Related Questions