mysql greatest n per group: latest message content and time for each conversation

Posted on

Question :

I have two tables, one for topics, and one for messages.
In terms of relationship, messages are posted within a topic, so each topic has many messages.

I am trying to make a query that would:
– list all the topics
– get the count of messages for each topic
– get the latest message posted for each topic (time and content)

I basically tried to do a query similar to:

SELECT
  topic.*,
  COUNT(msg.id),
  MAX(msg.time),
  msg.content

FROM topics

LEFT JOIN (
  SELECT
    msg.id,
    msg.time,
    msg.content,
    msg.topic_id
  FROM messages AS msg
  ORDER BY time DESC
) AS msg
ON msg.topic_id=topic.id

GROUP BY topic.id

However, this results in an error

Expression #… (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘msg.time’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I could just toggle the only_full_group_by setting, but my understanding is that I then have no guarantee that the msg.content will return me the latest message as expected.

I found many greatest n per group questions online, but most seem to only deal with a single table with the same table being queried in the query and subquery.

Is there any reliable way to achieve this with MySQL?

Answer :

SELECT t.*, 
       m4.count, 
       m4.time, 
       m4.content
FROM topics t
LEFT JOIN ( SELECT m1.topic_id, 
                   m3.count, 
                   m3.time, 
                   m1.content
            FROM messages m1
            INNER JOIN ( SELECT m2.topic_id, 
                                COUNT(m2.topic_id) count,  
                                MAX(m2.time) time
                         FROM messages m2
                         GROUP BY m2.topic_id ) m3 ON m1.topic_id = m3.topic_id
                                                  AND m1.time = m3.time ) m4 ON t.id = m4.topic_id

I assume that messages (topic_id, time) is unique.

Leave a Reply

Your email address will not be published.