获取 GROUP BY 语句的最新文本条目 [英] get latest text entry of GROUP BY statement

问题描述

我有一个用于保存邮件对话的数据库

I have a database where I keep mail conversations

table "conversations"
ID    SUBJECT
1     meeting on Friday

table "conversations_mails"
ID    CONVERSATION_ID    TEXT                               CREATED_ON
1     1                  "What about a meeting on Friday?"  2012-08-05 10:00:00
2     1                  "that's a good idea!"              2012-08-10 15:00:00

现在，我想显示一个对话概览页面，显示最新回复的截断文本版本.例如

Now, i want to display a conversation overview page, showing a truncated text version of the latest reply. eg

"Meeting on Friday"
That's a good ... 

我尝试通过 GROUP BY 来实现这一点.但我得到的是表格中的第一个回复(周五开会怎么样")，而不是最后一个这是个好主意".

I try to achieve this via GROUP BY. But what I get is the first reply in the table ("What about a meeting on Friday"), instead of the last "That's a good Idea".

这是我的 SQL 语句:

This is my SQL statement:

SELECT *, MAX(conversations_mails.created_on) As conversation_last_reply,
  MAX(conversations_mails.id) AS maxId
FROM conversations 
LEFT JOIN conversations_mails ON conversations_mails.conversation_id = conversations.id 
GROUP BY conversations.id 
ORDER BY conversation_last_reply DESC

我知道如何通过 MAX() 获取最高 ID，但是相应的 TEXT 条目呢?

I know how to get the highest ID via MAX(), but what about the corresponding TEXT entry?

谢谢！

推荐答案

试试这个-

SELECT *, MAX(conversations_mails.created_on) As conversation_last_reply,
  MAX(conversations_mails.id) AS maxId
FROM conversations 
LEFT JOIN conversations_mails ON conversations_mails.conversation_id = conversations.id 
WHERE conversations_mails.id = (select max(conversations_mails.id) from conversations_mails where conversations_mails.conversation_id = conversations.id)
GROUP BY conversations.id 
ORDER BY conversation_last_reply DESC

这篇关于获取 GROUP BY 语句的最新文本条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！