title: Selecting multiple "most recent by timestamp" in mysql
body: <p>I have a table containing log entries for various servers. I need to create a view with the most recent (by time) log entry for each <code>idServer</code>.</p>
  
  <pre><code>mysql&gt; describe serverLog;
  +----------+-----------+------+-----+-------------------+----------------+
  | Field    | Type      | Null | Key | Default           | Extra          |
  +----------+-----------+------+-----+-------------------+----------------+
  | idLog    | int(11)   | NO   | PRI | NULL              | auto_increment | 
  | idServer | int(11)   | NO   | MUL | NULL              |                | 
  | time     | timestamp | NO   |     | CURRENT_TIMESTAMP |                | 
  | text     | text      | NO   |     | NULL              |                | 
  +----------+-----------+------+-----+-------------------+----------------+
  
  mysql&gt; select * from serverLog;
  +-------+----------+---------------------+------------+
  | idLog | idServer | time                | text       |
  +-------+----------+---------------------+------------+
  |     1 |        1 | 2009-12-01 15:50:27 | log line 2 | 
  |     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
  |     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
  |     4 |        1 | 2009-12-01 10:20:30 | log line 0 | 
  +-------+----------+---------------------+------------+
  </code></pre>
  
  <p>What makes this difficult (for me) is:</p>
  
  <ul>
  <li>Entries for earlier dates/times may be inserted later, so I can't just rely on idLog.</li>
  <li>timestamps are not unique, so I need to use idLog as a tiebreaker for "latest".</li>
  </ul>
  
  <p>I can get the result I want using a subquery, but I can't put a subquery into a view. Also, I hear that subquery performance sucks in MySQL.</p>
  
  <pre><code>mysql&gt; SELECT * FROM (
      SELECT * FROM serverLog ORDER BY time DESC, idLog DESC
      ) q GROUP BY idServer;
  +-------+----------+---------------------+------------+
  | idLog | idServer | time                | text       |
  +-------+----------+---------------------+------------+
  |     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
  |     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
  +-------+----------+---------------------+------------+
  </code></pre>
  
  <p>What is the correct way to write my view?</p>