title: JPQL Query using max on a date field
body: <p>I need to query to find a record with the most recent date from a group of records. I've tried a bunch of stuff, with the most recent being something like this:</p>
  
  <pre>
  select msg, msg.createdDate from ImportedMessage msg where msg.siteId = ?1 and msg.createdDate = max(msg.createdDate) group by msg.createdDate
  </pre>
  
  <p>Unfortunately, everything I've tried has yielded some sort of error. The error I seem to get most is:</p>
  
  <pre>
  Caused by: java.sql.SQLException: Not in aggregate function or group by clause: 
  org.hsqldb.Expression@688c688c in statement [select importedme0_.IMPORTED_MSG_ID as 
  col_0_0_, importedme0_.CREATED_DATE as col_1_0_, max(importedme0_.CREATED_DATE) as 
  col_2_0_, importedme0_.IMPORTED_MSG_ID as IMPORTED1_1_, importedme0_.CREATED_BY as
  CREATED2_1_, importedme0_.CREATED_DATE as CREATED3_1_, importedme0_.UPDATED_BY as
  UPDATED4_1_, importedme0_.UPDATED_DATE as UPDATED5_1_, importedme0_.IMPORT_TYPE as
  IMPORT6_1_, importedme0_.MESSAGE as MESSAGE1_, importedme0_.PROCESSED_FLAG as
  PROCESSED8_1_, importedme0_.SITE_ID as SITE9_1_ from IMPORTED_MSG importedme0_ where
  importedme0_.SITE_ID=? and importedme0_.CREATED_DATE=max(importedme0_.CREATED_DATE) 
  group by importedme0_.CREATED_DATE]
      at org.hsqldb.jdbc.Util.throwError(Unknown Source)
      at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
      at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
      at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
      at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
      at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
      at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1616)
      at org.hibernate.loader.Loader.doQuery(Loader.java:717)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
      at org.hibernate.loader.Loader.doList(Loader.java:2449)
      ... 52 more
  
  </pre>
  
  <p>I believe that what this is telling me is that I don't have the appropriate things in my select clause to allow the group to work. However, I've tried all sorts of combinations, and everything leads back to this error.</p>
  
  <p>Can someone give me a clue what I'm doing wrong here?</p>