JPQL query using max in a date field

Question

I need to query to find the record with the most recent date from the record group. I tried a bunch of things, and the last one was 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 tried gave some error. The error I seem to get the most:

 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∗34)
     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 this tells me that I do not have the relevant things in my select clause to allow the group to work. Nevertheless, I tried all kinds of combinations, and everything returns to this error.

Can someone let me know what I'm doing wrong here?

Answer 1

Well, I think the moderator didn’t bother to read the editing, which translated the answer: the researcher’s comment about the request’s intention:

“I have a table that contains a list of data elements (id, message (string), siteId (string), createdDate (Timestamp). What I need to do is select the siteId file and then find the entry in this group with the last createdDate. "

Decision:

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); 

Answer 2

In sleep mode, using the Oracle10 dialect, translate it to WHERE ROWNUM & lt; = 1.