title: JPQL get most recent rows
body: <p>Let's say I have the following tables</p>
  
  <pre><code>my_profile_data
  -------------
  integer: my_profile_data_id
  integer: profile_id
  integer: profile_data_type_id
  date: date_changed
  string: value
  
  my_profile
  -------------
  integer: profile_id
  string: name
  
  profile_data_type
  -------------
  integer: profile_data_type_id
  string: name
  </code></pre>
  
  <p>I want to get the most recent profile information for each profile data type.  In plain SQL this would look something like:</p>
  
  <pre><code>select mpd.profile_id, mpd.profile_data_type_id, mpd.value, max(mpd.date_changed) 
  from my_profile_data mpd, my_profile mp 
  where mpd.profile_id = mp.profile_id and mp.name='The Profile I Want' 
  group by mpd.profile_data_type_id
  </code></pre>
  
  <p>I've tried different variants of the following JPQL query, but can't get it to work.</p>
  
  <pre><code>SELECT mpd FROM MyProfileData mpd LEFT JOIN
       (SELECT mpd.profileId profileId, MAX(mpd.dateChanged) FROM MyProfileData mpd
       LEFT JOIN mp.profile
       WHERE mp.name = :name
       GROUP BY mpd.profileDataTypeId) recent
  ON (rp.profileid = recent.profileId)
  </code></pre>
  
  <p>Is this query doable in JPA?</p>
  
  <p>I'm using EclipseLink as my JPA provider.</p>
  
  <p>The innermost exception I get when I try to run this is</p>
  
  <pre><code>Caused by: NoViableAltException(81!=[506:7: (n= joinAssociationPathExpression ( AS )? i= IDENT | t= FETCH n= joinAssociationPathExpression )])
      at org.eclipse.persistence.internal.jpa.parsing.jpql.antlr.JPQLParser.join(JPQLParser.java:3669)
      ... 73 more
  </code></pre>