title: Jpql selecting the one maximum row from each group
body: <p>I am relatively new to JPA and I would like to solve the following problem using jpql exclusively (note that the implementation I am using is Datanucleus): I have a table of versioned entities, and I would like to get the latest version for all entities in the table (i.e. I have an entity class which has an id (which uniquely identifies a row, an entityId (which identifies the entity itself throughout versions) and a timestamp; I would like to get the latest version entity for all entityId). My current code goes as follows: </p>
  
  <pre><code>    String innerQueryString = "SELECT entity.entityId, max(entity.timestamp) " +
                    "FROM Entity entity" +
                    "GROUP BY entity.entityId";
  
      Query getQuery = getEntityManager().createQuery(innerQueryString);
  
      List&lt;Object[]&gt; queryRes = getQuery.getResultList();
      List&lt;IEntity&gt; ret = new ArrayList&lt;IEntity&gt;();
  
      for (Object[] res : queryRes) { 
          ret.add(getEntity((Long)res[0], (Date)res[1]));
      }
  
      return ret;
  </code></pre>
  
  <p>Where getEntity gets the entity data for the specified entityId, timestamp. I have found several resources on how this code would work in sql here <a href="http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/" rel="noreferrer">http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/</a> but I cannot manage to create a jpql version of it. Help will be greatly appreciated, thanks. </p>