在日期字段上使用max的JPQL查询 [英] JPQL Query using max on a date field

问题描述

我需要查询以从一组记录中查找具有最近日期的记录.我已经尝试了很多东西，最近的是这样的:

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

我相信这是在告诉我的是，我的select子句中没有适当的内容来允许组工作.但是，我尝试了各种组合，一切都导致了此错误.

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?

推荐答案

好吧，我猜主持人没有费心阅读使答案向上移动的编辑内容: 发问者对查询意图的评论:

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

我有一个表，该表包含一个数据元素列表(id，消息(字符串)，siteId(字符串)，createdDate(时间戳)).我需要做的是通过siteId选择，然后在其中找到记录分组，并带有最近的createdDate."

"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."

解决方案:

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

这篇关于在日期字段上使用max的JPQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！