[FIXED] Last record in each group

Issue

I use oracle 11.2, I try to get last record in each group.

In my ce table I have id, date, name field.

select ce1.*
from ce ce1
inner join 
  (select account_id, max(date) as max_date from ce group by account_id ) as group_id
   on ce1.account_id=group_id.account_id
  and ce1.date=group_id.max_date
where ce1.account_id in (....)

that work well

I try to convert it in jpql without success, my jpa implementation is hibernate

select ce1
    from ce ce1
    inner join 
      (select accountId as accountId, max(date) maxDate from ce group by accountId ) as group_id
       on ce1.accountId=group_id.accountId
      and ce1.date=group_id.maxDate
    where ce1.accountId in (....)

Jpa don't seem to like innerjoin and the max

unexpected token ( unexpected token max

Solution

Assuming the entity Ce:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Ce> cq = cb.createQuery(Ce.class);

Root<Ce> rootCe = cq.from(Ce.class);

Subquery<Date> sqMaxDate = cq.subquery(Date.class);
Root<Ce> sqRootCe = sqMaxDate.from(Ce.class);
sqMaxDate.where(cb.equal(rootCe.get(Ce_.accountId),sqRootCe.get(Ce_.accountId)));
sqMaxDate.select(cb.max(sqRootCe.get(Ce_.date)));

cq.where(cb.and(cb.equal(rootCe.get(Ce_.date),sqMaxDate.getSelection()),
                 rootCe.get(Ce_.accountId).in([...])));

This query result:

Select * from CE ce1
Where ce1.account_id in ([...])
    and ce1.date = (
        select max(date) from CE ce2
        where ce1.account_id = ce2.account_id
    );

Without the entity I believe that this solution is valid. We get the maximum date for each acount id and set it as a condition of the main query

Answered By - JLazar0