java JPA，playframework，在分组之前选择order by

Question

如何在“JPA”中编写查询 那将在分组之前订购

例如：

select m from Message m where m.id = (
  select distinct m2.conversation_id
    from Message m2
   where m2.from = 100 or m2.to = 100
)
group by m.conversation_id order by m.date desc

我想要每个对话id的最后（最近）消息

Answer 1

简而言之，你不能把订单放在分组之前；SQL要求ORDER BY位于GROUP BY之后

但是，您可以使用下面冗长的方法或使用不同的ORDER by子句构造查询，以实现所需的结果：

ORDER BY m.conversation_id, m.date DESC

在从数据库获取数据的代码中，为返回的每个新会话ID选择第一行

和往常一样，我分阶段构建查询。首先，为涉及“用户ID 100”的每个对话ID查找最近的消息时间戳：

SELECT m2.conversation_id, MAX(m2.date) AS date
  FROM Message AS m2
 WHERE m2.from = 100 OR m2.to = 100
 GROUP BY m2.conversation_id

现在，将其与原始表连接以获得感兴趣的行：

SELECT m1.*
  FROM Message AS m1
  JOIN (SELECT m2.conversation_id, MAX(m2.date) AS date
          FROM Message AS m2
         WHERE m2.from = 100 OR m2.to = 100
         GROUP BY m2.conversation_id
       ) AS m3
    ON m1.conversation_id = m3.conversation_id AND m1.date = m3.date
 ORDER BY m1.date DESC;