title: JPA Criteria Query to find the MAX ID for each unique name
body: <p>EDIT:  Apparently my prior question was not sufficient so I will try to make it more clear with this try.</p>
  
  <p>I have a set of rows in the table, selected by predicate <strong>p1</strong> below each of which have a unique value in the <strong>id</strong> column.  The <strong>pathName</strong> column may have multiple instances of the same value.   What want is to return only the entities that have a distinct <strong>pathName</strong> with the highest value <strong>id</strong>.</p>
  
  <pre><code> CriteriaBuilder builder = em.getCriteriaBuilder();
   CriteriaQuery&lt;TextDocument&gt; query = builder.createQuery(TextDocument.class);
   Root&lt;TextDocument&gt; root = query.from(TextDocument.class);
  
   // descending ids, and group similar names together
  
   query.orderBy(builder.desc(root.get(TextDocument_.id)));
   query.groupBy(root.get(TextDocument_.pathName));
  
   // filter
  
   Predicate p1 = builder.equal(root.get(TextDocument_.parent), rootdoc);
   Predicate p2 = builder.equal(root.get(TextDocument_.id), builder.max(root.get(TextDocument_.id)));
   query.where(p1, p2);
  </code></pre>
  
  <p>The predicate <strong>p1</strong> selects the documents that all have the same parent document and this of course works as long as it is by itself.</p>
  
  <p>Predicate <strong>p2</strong> is all wrong, however.  Adding it causes an exception <em>ERROR 42903: Invalid use of an aggregate function.</em>.   </p>
  
  <p>The solution to a similar query problem is shown <a href="https://stackoverflow.com/questions/5554075/get-last-distinct-set-of-records">here</a>  in SQL but I want to do it using JPA Criteria Query.   I can't figure out how to make the nested SELECT syntax work.</p>
  
  <p>To reiterate the problem with my own example I have the following data set</p>
  
  <pre><code>id  parent  pathName  
  --- ------- --------- 
  101     22  alpha
  130     22  beta
  250     22  charlie
  251     22  alpha
  339     22  beta
  400     22  alpha
  401     22  delta
  </code></pre>
  
  <p>The correct result (for parent=22) would be:</p>
  
  <pre><code>id  parent  pathName  
  --- ------- --------- 
  250     22  charlie
  339     22  beta
  400     22  alpha
  401     22  delta
  </code></pre>
  
  <p>Can anyone help me finish this query?</p>