0

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?

Improve this question
2

1 Answer 1

Reset to default
2 
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.

Improve this answer
1
  • Works great! I actually came up with a similar solution in the meantime. One additional thing I forgot to mention is that the query is also supposed to compute the number of unread message for a specific user, based on some data on a third table, but I found a way to adjust the logic of this solution to support that as well. Thanks!
    – Simon Ninon
    Commented Mar 28, 2019 at 6:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.