title: ORDERing within JOIN in MySQL - latest messsage per user
body: <p>Got two versions of a query. Neither does quite what I need..</p>
  
  <p>Trying to return all users, along with the time of the latest message sent <em>either</em> <strong>to OR from</strong> that user. An additional flag shows whether that last message was <strong>from</strong> the user in the current row (hence whether they're 'awaiting response').</p>
  
  <p>Finally, the entire result set needs to be ordered to have those users 'awaiting response' first, and secondarily ordered by the time of the latest message.</p>
  
  <p>Version 1:</p>
  
  <pre><code>SELECT `users`.*,
  MAX(`messages`.`time_sent`) AS `_message_time_sent`,
  (`messages`.`user_id` = `users`.`id`) AS `_message_awaiting_response` 
  FROM `users` LEFT JOIN `messages` 
  ON (`messages`.`user_id` = `users`.`id` OR `messages`.`to_user_id` = `users`.`id`) 
  GROUP BY `users`.`id` 
  ORDER BY `_message_awaiting_response` DESC, `messages`.`time_sent` DESC
  </code></pre>
  
  <p>The problem with version 1: the '_message_sent_time' is correct (via MAX), but the '_message_awaiting_response' flag is always derived from whichever message appeared first in the join before the MAX() occurred, which may not be the most recent message.</p>
  
  <p>Version 2:</p>
  
  <pre><code>SELECT DISTINCT `users`.*,
  `messages`.`time_sent` AS `_message_time_sent`,
  (`messages`.`user_id` = `users`.`id`) AS `_message_awaiting_response`
  FROM `users` LEFT JOIN `messages`
  ON (`messages`.`user_id` = `users`.`id` OR `messages`.`to_user_id` = `users`.`id`) 
  GROUP BY `users`.`id` 
  ORDER BY `_message_awaiting_response` DESC, `messages`.`time_sent` DESC
  </code></pre>
  
  <p>With version 2, the problem is largely the same - due to there being no MAX() aggregate function, the first message found in the join (not the most recent) is singled-out by DISTINCT.</p>
  
  <p>Is there a simple way of making sure the latest message gets targeted here?</p>
  
  <p>P.S. It needs to work in MySQL 4 ;)</p>