title: Returning last record in a subquery - MySQL
body: <p>So I am creating a messaging system similar to Facebook's style for my website (I know right). What I intend to happen is list all conversations for the user and also return the last message in the conversation. Problem is I am getting the first message when doing a GROUP BY. Perhaps I am going about it in the wrong way.</p>
  <p>The structure of the database consists of three tables. <em><strong>conversations</strong></em>, <em><strong>conversations_assign</strong></em>, <em><strong>conversations_msg</strong></em></p>
  <p><em><strong>conversations</strong></em> just stores the outside data of the conversation and the important thing is the id</p>
  <p><em><strong>conversations_assign</strong></em> does just that. It lists the <em><strong>userid</strong></em> of everyone in the conversation</p>
  <p><em><strong>conversations_msg</strong></em> stores obviously the message, user who posted with other data.</p>
  <p>Everything is tied together with <em><strong>conversations.id</strong></em> = <em><strong>conv_id</strong></em></p>
  <p>Here is the query I have and as I said from above with GROUP BY it is returning the first result. Taking off GROUP BY and adding LIMIT 1 literally returns one message and the rest are null. Any help is appreciated, thanks!</p>
  <pre><code>SELECT conversations.id,
      conversations_assign.num_unread,
      message
  FROM conversations_assign
  INNER JOIN conversations ON conversations_assign.conv_id=conversations.id                                     
  LEFT JOIN (
      SELECT conv_id, message, msg_time
      FROM conversations_msg
      GROUP BY conv_id
  ) AS message ON message.conv_id=conversations_assign.conv_id
  WHERE conversations_assign.userid='XXsomeidXX'
  ORDER BY message.msg_time DESC
  </code></pre>