JPA criteria select all instances with maximum values ​​in their groups

Question

Is there a way to write the equivalent of the following query with JPA 2 CriteriaBuilder ?

 select * from season s1 where end = ( select max(end) from season s2 where s1.contest_id=s2.contest_id ); 

In JPQL, this query:

 Select s1 from Season s1 where s1.end = ( select max(s2.end) from Season s2 where s1.contest=s2.contest ) 

Answer 1

This should work, with contest being either the main Integer property or the ManyToOne property pointing to another non-essential Entity.

 EntityManger em; //to be injected or constructed CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Season> cq = cb.createQuery(Season.class); Subquery<Date> sq = cq.subquery(Date.class); Root<Season> s1 = cq.from(Season.class); Root<Season> s2 = sq.from(Season.class); sq.select(cb.greatest(s2.get(Season_.end))); sq.where(cb.equal(s2.get(Season_.contest), s1.get(Season_.contest))); cq.where(cb.equal(s1.get(Season_.end), sq)); List<Season> result = em.createQuery(cq).getResultList();