What are the alternatives to using an ORDER BY in a Subquery in the JPA Criteria API?
<p>Consider the following two tables:</p>

<ol>
<li><code>Project ( id, project_name)</code></li>
<li><code>Status ( id, id_project, status_name)</code></li>
</ol>

<p>Where <code>Status</code> contains all statuses in which a <code>Project</code> has been. </p>

<p>Lets say we want to query all projects for which the latest status has the name "new". The Sql query that I come up with is:</p>

<pre><code>SELECT q.id_project FROM status q
WHERE q.status_name like 'new'
AND q.id IN (
    SELECT TOP 1 sq.id from status sq
    WHERE q.id_project = sq.id_project 
    ORDER BY sq.id DESC )
</code></pre>

<p>I'm trying to replicate the above query using Criteria API and I noticed that the class <a href="http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaQuery.html" rel="noreferrer">CriteriaQuery</a> has the method <code>orderBy</code> but the class <a href="http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/Subquery.html" rel="noreferrer">Subquery</a> doesn't.</p>

<p>The Criteria Query that I have come up with so far is:</p>

<pre><code>CriteriaQuery&lt;Project&gt; q = criteriaBuilder.createQuery(Project.class);
Root&lt;Status&gt; qFrom       = q.from(Status.class);
Subquery&lt;Integer&gt; sq     = q.subquery(Integer.class);
Root&lt;Status&gt; sqFrom      = sq.from(Status.class);

sq.select(sqFrom.get(Status_.id))
  .where(criteriaBuilder.equal(sqFrom.get(Status_.project), qFrom.get(Status_.project))
</code></pre>

<p>I got stuck here because <code>Subquery sq</code> doesn't have any method for sorting its results and returning only the latest one.</p>

<p>What are the alternatives to sorting the subquery in order to get the desired result in the scenario described above?</p>