title: SQL SELECT rows with max date
body: <p>I have table <code>message</code></p>
  
  <pre><code>`id` int(10) unsigned NOT NULL AUTO_INCREMENT,   
  `from_user_id` int(10) unsigned NOT NULL
  `to_user_id` int(10) unsigned NOT NULL,  
  `request_id` int(10) unsigned DEFAULT NULL,
  `text` varchar(1000) NOT NULL,
  `date` datetime DEFAULT NULL,
  </code></pre>
  
  <p>And data inside:</p>
  
  <pre><code>1   4   1   3   Text1       01.05.2014 14:02
  2   4   1   3   Text2       02.05.2014 14:02
  3   4   1   3   Text3       03.05.2014 14:02
  4   4   1   4   Text4       04.05.2014 14:02
  5   4   1   4   Text5       05.05.2014 14:02
  6   2   1   3   Text6       06.05.2014 14:02
  7   2   1   3   Text7       07.05.2014 14:02
  </code></pre>
  
  <p>For example first line represents following:
  User 4 sent message 'Text1' regarding request 3 on 01.05.2014 14:02 to User 1.</p>
  
  <p>I want to get all latest messages to user 1 for different requests.
  The result I am expecting to get is:</p>
  
  <pre><code>3   4   1   3   Text3       03.05.2014 14:02
  5   4   1   4   Text5       05.05.2014 14:02
  7   2   1   3   Text7       07.05.2014 14:02
  </code></pre>
  
  <p>I have already tried:</p>
  
  <pre><code>SELECT * FROM `message` `t` 
     WHERE `to_user_id`='1' AND 
     `date` = 
        (SELECT MAX(`date`) FROM 
           (SELECT * FROM `message` `t3` 
              WHERE `t3`.`request_id`=`t`.`request_id`
           ) `t2` 
           WHERE `t2`.`from_user_id`=`t`.`from_user_id`
        );
  </code></pre>
  
  <p>But it returned error <code>#1054 - Unknown column 't.request_id' in 'where clause'</code> </p>
  
  <p>Seems like alias <code>t</code> is not reachable from inside <code>WHERE</code> clause.</p>
  
  <p>Any suggestions?</p>
  
  <p>EDIT:</p>
  
  <p>The table consists of offer_id and request_id. Only one of them can be NULL. Thanks to @fancyPants' answer I found out correct solution</p>
  
  <pre><code>SELECT * FROM `message` `t` 
  WHERE `to_user_id`='1' AND 
  `date` = 
     (SELECT MAX(`date`) FROM `message` `t2` 
      WHERE `t2`.`from_user_id`=`t`.`from_user_id`
      AND (`t2`.`request_id`=`t`.`request_id` OR 
      `t2`.`offer_id`=`t`.`offer_id`)
     );
  </code></pre>