JPA CriteriaBuilder for join in subquery
<p>I have SQL statement something like this : </p>

<pre><code>SELECT * FROM person inner join (select max(validityId) as maxID from person group by personId) maxID on maxID.maxID = person.validityid;
</code></pre>

<p>So it give me "distinct" row for personID:</p>

<p>If I have table like this : </p>

<pre><code>|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    10      | 400     |
|    1       |    11      | 500     |
|    1       |    12      | 600     |
|    2       |    13      | 700     |
|    2       |    14      | 800     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |
</code></pre>

<p>it would return </p>

<pre><code>|  personID  |  validitID | value   |
-------------------------------------------
|    1       |    12      | 600     |
|    2       |    15      | 900     |
|    4       |    16      | 1000    |
</code></pre>

<p>Now I try to do this by JPA CriteriaBuilder.</p>

<p>My first thought was sub query : </p>

<pre><code>final CriteriaBuilder cb = this.em.getCriteriaBuilder();
final CriteriaQuery&lt;Person&gt; cq = cb.createQuery(Person.class);
final Root&lt;Person&gt; root = cq.from(Person.class);
cq.select(root);

final Subquery&lt;Long&gt; subquery = cq.subquery(Long.class);
final Root&lt;Person&gt; validityIDSQ = subquery.from(Person.class);
subquery.select(validityIDSQ.get(Person_.validityId));
subquery.groupBy(validityIDSQ.get(Person_.personId));

cb.where(cb.in(root.get(Person_.validityId)).value(subquery));
</code></pre>

<p>But this produce error </p>

<pre><code> ERROR: column "person1_.validityid" must appear in the GROUP BY clause or be used in an aggregate function
</code></pre>

<p>How to this right?</p>

<p>Marko</p>