title: JPA Select latest instance for each item
body: <p>Let's say I have a Meeting entity.  Each meeting has a single attendee and a meeting date.  Within my meeting table I may have multiple meetings for each attendee, with different dates for each.  I need a JPA query that will select only the latest meeting for all attendees.  For instance, if my table looks like this</p>
  
  <pre><code>Meeting ID | Attendee ID | Meeting Date
  1          | 1           |  6/1/2011
  2          | 2           |  6/1/2011
  3          | 1           |  6/6/2011
  4          | 3           |  6/6/2011
  </code></pre>
  
  <p>My result should be</p>
  
  <pre><code>Meeting ID | Attendee ID | Meeting Date
  2          | 2           |  6/1/2011
  3          | 1           |  6/6/2011
  4          | 3           |  6/6/2011
  </code></pre>
  
  <p>Using JPA 2 against postgres.  Meeting has 1-1 to attendee and a simple timestamp date.  I suspect I'm going to need to do a group by and max(blah) and maybe a join to myself, but I'm not sure of the best way to approach this.</p>
  
  <p><strong>Update:</strong>
  After spending the evening playing with this, I still do not have an acceptable JPQL solution to this.  Here is what I have so far:</p>
  
  <pre><code>select m from Meeting m 
  where m.meetingDate in 
      ( select max(meet.meetingDate) 
        from Meeting meet group by meet.attendee )
  </code></pre>
  
  <p>I have various other conditions that are not relevant to this question, like filtering by attendee department and whatnot.  The only reason this works is because we are tracking meeting date to the second (or finer) and the chance that there will be two meetings at exactly the same time is minimal.  We are putting some java stuff around it to keep only hte last meeting for each attendee just in case we do get two at the same time, but that's a pretty crappy solution.  It really shouldn't be too difficult to get it all in a query, but I have yet to figure it out.</p>
  
  <p><strong>Update2:</strong> Adding sql tag because if I need to use sql to create a view and create a JPA object to map to the view I'm ok with that.</p>