MySQL how to solve One-to-one relation

I have a One-to-One Chat implementation with MySQL. The goal is to got for one user all unique Conversations with the last message. Even I’m using MAX on the primary key and group by 2nd column MySQL reject this.

The below query give the error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘m.id’ which is not functionally dependent on columns in GROUP BY clause;

I’m using MySQL Version 8.

What I’m missing here?

Message table

id | member_a_id | member_a_status | member_b_id | member_b_status | conversation_id | content | created_dt

Conversation table

id | member_a_id | member_a_status | member_b_id | member_b_status | conversation_id | content | created_dt | last_activity_dt

Query:

SELECT
  MAX(m.id) AS msgid, m.*
FROM
  message m
WHERE m.conversation_id IN
  (SELECT
    c.id
  FROM
    conversation c
  WHERE (
      c.member_a_id = 600
      AND c.member_a_status NOT IN (0, 3)
    )
    OR (
      c.member_b_id = 600
      AND c.member_b_status NOT IN (0, 3)
    ))
GROUP BY m.conversation_id
ORDER BY m.id DESC;

Answer

Without sample data, your question is a bit hard to follow. However, you should be using window functions if you are using MySQL 8+. The query would look something like this:

SELECT cm.*
FROM (SELECT m.*,
             ROW_NUMBER() OVER (PARTITION BY m.conversation_id ORDE BY m.id DESC) as seqnum
      FROM message m JOIN
           conversion c
           ON m.conversation_id = c.conversation_id
      WHERE 600 IN (c.member_a_id, c.member_b_id) AND
            c.member_a_status NOT IN (0, 3)
     ) mc
WHERE seqnum = 1;