title: SELECT ... ORDER BY field IN (SELECT ...) not working on duplicate field
body: <p>I need to sort a MySQL table after two columns, but not like a regular sort. In a chat application i want to get results sorted by timestamp field, but also grouped by their sender</p>
  
  <pre><code>CREATE TABLE messages (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sender int(10) unsigned NOT NULL,
    message char(255) NOT NULL,
    PRIMARY KEY (id)
  ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
  
  INSERT INTO messages VALUES ('1', '2011-02-28 01:58:24', '3', '1');
  INSERT INTO messages VALUES ('2', '2011-02-28 01:58:28', '3', '2');
  INSERT INTO messages VALUES ('3', '2011-02-28 01:58:35', '1', '5');
  INSERT INTO messages VALUES ('4', '2011-02-28 01:58:36', '2', '7');
  INSERT INTO messages VALUES ('5', '2011-02-28 01:58:38', '3', '3');
  INSERT INTO messages VALUES ('6', '2011-02-28 01:58:39', '2', '8');
  INSERT INTO messages VALUES ('7', '2011-02-28 01:58:40', '1', '6');
  INSERT INTO messages VALUES ('8', '2011-02-28 01:58:41', '3', '4');
  </code></pre>
  
  <p>So:</p>
  
  <p><strong>ORDER BY timestamp, sender</strong> is useless</p>
  
  <p><strong>ORDER BY sender, timestamp</strong> is pretty ok, but it will not show older messages first</p>
  
  <pre><code>SELECT timestamp, sender, message FROM messages ORDER BY sender IN (SELECT DISTINCT sender FROM messages ORDER BY timestamp), timestamp;
  </code></pre>
  
  <p>does not work as i suppose it must work. And here is what i expected from this query (ofc is not what i get from this query :P):</p>
  
  <pre><code>+---------------------+--------+---------+
  | timestamp           | sender | message |
  +---------------------+--------+---------+
  | 2011-02-28 01:58:24 |      3 | 1       |
  | 2011-02-28 01:58:28 |      3 | 2       |
  | 2011-02-28 01:58:38 |      3 | 3       |
  | 2011-02-28 01:58:41 |      3 | 4       |
  | 2011-02-28 01:58:35 |      1 | 5       |
  | 2011-02-28 01:58:40 |      1 | 6       |
  | 2011-02-28 01:58:36 |      2 | 7       |
  | 2011-02-28 01:58:39 |      2 | 8       |
  +---------------------+--------+---------+
  </code></pre>
  
  <p>Any ideas? links? tips? anything?
  Thanks in advance, any questions are welcome</p>