title: Select the latest 3 records for each ID in a table
body: <p>I have a table with a composite primary key (<code>ID</code>, <code>Date</code>) like below.</p>
  
  <pre>
  +------+------------+-------+
  |  ID  |    Date    | Value |
  +------+------------+-------+
  |   1  | 1433419200 |   15  |
  |   1  | 1433332800 |   23  |
  |   1  | 1433246400 |   41  |
  |   1  | 1433160000 |   55  |
  |   1  | 1432900800 |   24  |
  |   2  | 1433419200 |   52  |
  |   2  | 1433332800 |   23  |
  |   2  | 1433246400 |   39  |
  |   2  | 1433160000 |   22  |
  |   3  | 1433419200 |   11  |
  |   3  | 1433246400 |   58  |
  |  ... |    ...     |  ...  |
  +------+------------+-------+
  </pre>
  
  <p>There is also a separate index on <code>Date</code> column. The table is of moderate size, currently ~600k row and growing by ~2k everyday.</p>
  
  <p>I want to do a single SELECT query that returns the latest 3 records (ordered by <code>Date</code> timestamp) for each <code>ID</code>. For each given <code>ID</code>, the <code>Date</code> values are always unique, so no need to worry about ties for <code>Date</code> here.</p>
  
  <p>I've tried a self-join approach, inspired by <a href="https://stackoverflow.com/a/12114175/548240">this answer</a>, but it took quite a few seconds to run and returned nothing:</p>
  
  <pre><code>SELECT p1.ID, p1.Date, p1.Value FROM MyTable AS p1
  LEFT JOIN MyTable AS p2 
  ON p1.ID=p2.ID AND p1.Date&lt;=p2.Date
  GROUP BY p1.ID
  HAVING COUNT(*)&lt;=5
  ORDER BY p1.ID, p1.Date DESC;
  </code></pre>
  
  <p>What would be a fast solution here?</p>