title: How do I rewrite this particular JPQL query with SELECT MAX() to CriteriaQuery?
body: <p>I am quite new to JPA and I have encountered a problem with understanding one particular query. I have rewritten in to the CriteriaQuery but the result and the query translated to SQL is not correct.</p>
  
  <p>Background situation: I have a table of store transaction (moves) and the current amount in the store is defined as a sum of all changes. Now I want to select and display the last moves as they also contain an information about the resulting amount on store.</p>
  
  <p>So, this is the query in JPQL:</p>
  
  <pre><code>SELECT m FROM move m WHERE m.id = (
    SELECT MAX(o.id) FROM move o WHERE (o.item = m.item AND m.cell.store = :s)
  </code></pre>
  
  <p>I tried to rewrite it to the following CriteriaQuery:</p>
  
  <pre><code>CriteriaBuilder builder = model.getCriteriaBuilder();
  CriteriaQuery&lt;Move&gt; query = builder.createQuery(Move.class);
  Root&lt;Move&gt; root = query.from(Move.class);
  
  Subquery&lt;Long&gt; subquery = query.subquery(Long.class);
  Root&lt;Move&gt; subroot = subquery.from(Move.class);
  subquery.select(builder.max(subroot.get("id").as(Long.class)));
  
  subquery.where(builder.and(
      builder.equal(
          subroot.get("item").get("id"),
          root.get("item").get("id")),
      builder.equal(
          subroot.get("cell").get("store").as(Store.class),
          store)));
  
  Expression&lt;Boolean&gt; where = builder.equal(
      root.get("id"),
      subquery);
  
  query.where(where);
  
  return model.getList(query);
  </code></pre>
  
  <p>The incorrect SQL query produced is following:</p>
  
  <pre><code>SELECT t0.id, (...) FROM move t0 WHERE (t0.id = (
    SELECT MAX(t1.id) FROM item t3, item t2, move t1 
    WHERE (((t2.id = t3.id) AND 
          (t1.cell_store = ?)) AND 
          ((t2.id = t1.item_ref) AND 
          (t3.id = t0.item_ref))))
  )
  </code></pre>
  
  <p>I do not understand why there is a double cross join in the subquery. Thank you for helping!</p>