title: MySQL how to solve One-to-one relation
body: <p>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. </p>
  
  <p>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;</p>
  
  <p>I'm using MySQL Version 8.</p>
  
  <p>What I'm missing here?</p>
  
  <p>Message table</p>
  
  <pre><code>id | member_a_id | member_a_status | member_b_id | member_b_status | conversation_id | content | created_dt
  </code></pre>
  
  <p>Conversation table</p>
  
  <pre><code>id | member_a_id | member_a_status | member_b_id | member_b_status | conversation_id | content | created_dt | last_activity_dt
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>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;
  </code></pre>