title: SQL select latest message of every conversation received AND sent
body: <p>This question <a href="https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column"> SQL select only rows with max value on a column </a> doesn't solve my problem although it has been marked as duplicate. </p>
  
  <p>It assumes my columns <code>from_id</code> and <code>to_id</code> are primary keys, when they don't have such constraint (see code provided bellow). If they were primary keys, I couldn't store my messages in the same table. As a result the SQL query of this answer prints all duplicates multiple times, which is <strong>not</strong> what I want. Please see expected behaviour bellow.</p>
  
  <p>Expected behaviour : I need to select the latest message from all conversations, <strong>regardless of whether</strong> the user is only sender, recipient, or both. Each conversation/thread <strong>should only be displayed once</strong>. </p>
  
  <p>Example : when querying this table, my SQL statement should only output msg3 and msg4, ignoring all the previous messages John and Alice exchanged. </p>
  
  <p><a href="https://i.stack.imgur.com/7WzAw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7WzAw.png" alt="enter image description here"></a></p>
  
  <p>Here is the closest query I could write. Problem is this query only selects conversations where user received a message. I'm stuck adding conversations where user is only sender (he didn't get any reply) to the selection.</p>
  
  <pre><code>SELECT * FROM messages where `to_id` = '1' GROUP BY `from_id` ORDER BY `send_date` ASC
  </code></pre>
  
  <p>Here are <code>users</code> and <code>messages</code> tables:</p>
  
  <pre><code>CREATE TABLE users (
      id INT(11) AUTO_INCREMENT PRIMARY KEY, 
      name VARCHAR(128) NOT NULL
  );
  
  CREATE TABLE messages (
      id INT(11) AUTO_INCREMENT PRIMARY KEY, 
      to_id INT(11) NOT NULL,   //recipient id to match to current user id
      from_id INT(11) NOT NULL, //sender id to match to current user id
      send_date DATETIME DEFAULT CURRENT_TIMESTAMP,
      content TEXT
  );
  </code></pre>
  
  <p><strong>Question:</strong> How can I do this using a single SQL query ? Or should I change my data structure using three tables instead of one ?</p>