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?