JPQL Query using max on a date field

Question

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:

select msg, msg.createdDate from ImportedMessage msg where msg.siteId = ?1 and msg.createdDate = max(msg.createdDate) group by msg.createdDate

Unfortunately, everything I've tried has yielded some sort of error. The error I seem to get most is:

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

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.

Can someone give me a clue what I'm doing wrong here?

Answer 1

Well I guess the moderator didn't bother to read the edit that moved the answer up: comment from questioner on intent of query:

"I have a table that conains a list of data elements (id, message(string), siteId (string), createdDate (Timestamp). What I need to do is select by the siteId, then find the record in that group with the most recent createdDate."

Solution:

Query query = entityManagerReference.createQuery(
"SELECT msg FROM ImportedMessage msg " 
+ "WHERE msg.siteId = :siteId ORDER BY msg.createDate desc");

query.setParameter("siteId", 12345);
query.setMaxResults(1);