EDIT: Apparently my prior question was not sufficient so I will try to make it more clear with this try.

I have a set of rows in the table, selected by predicate **p1** below each of which have a unique value in the **id** column. The **pathName** column may have multiple instances of the same value. What want is to return only the entities that have a distinct **pathName** with the highest value **id**.

```
 CriteriaBuilder builder = em.getCriteriaBuilder();
 CriteriaQuery<TextDocument> query = builder.createQuery(TextDocument.class);
 Root<TextDocument> 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);

```

The predicate **p1** selects the documents that all have the same parent document and this of course works as long as it is by itself.

Predicate **p2** is all wrong, however. Adding it causes an exception *ERROR 42903: Invalid use of an aggregate function.*. 

The solution to a similar query problem is shown [here](https://stackoverflow.com/questions/5554075/get-last-distinct-set-of-records) 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.

To reiterate the problem with my own example I have the following data set

```
id  parent  pathName  
--- ------- --------- 
101     22  alpha
130     22  beta
250     22  charlie
251     22  alpha
339     22  beta
400     22  alpha
401     22  delta

```

The correct result (for parent=22) would be:

```
id  parent  pathName  
--- ------- --------- 
250     22  charlie
339     22  beta
400     22  alpha
401     22  delta

```

Can anyone help me finish this query?