title: SQL query to get historic data from table
body: <p>I have a table that stores the history of status changes to a person like this:</p>
  
  <pre><code>id | username | date | status
  </code></pre>
  
  <p>The date field is when the status was updated and the status field contains the new status that the person has since that date.</p>
  
  <p>So the date in my table could be something like this:</p>
  
  <pre>
  1 | serafeim | 2012-03-03 | "NEW"
  2 | john | 2012-03-05 | "NEW"
  3 | serafeim | 2012-03-13 | "PENDING"
  4 | serafeim | 2012-03-15 | "OLD"
  5 | john | 2012-03-05 | "PENDING"
  </pre>
  
  <p>etc etc.</p>
  
  <p>Now, I'd like to have a query that for a specific date in the past will retrieve the status that each user had then. For instance, for 2012-04-14 I'd like to get the following results</p>
  
  <pre>
  serafeim | "PENDING"
  john | "NEW"
  </pre>
  
  <p>for 2012-03-04 I should get</p>
  
  <pre>
  serafeim | "NEW"
  </pre>
  
  <p>Can anybody think of an SQL query that will do that ? I don't want to do this programatically ! I'm using mysql but I don't think that that's relative to my problem...</p>
  
  <p>Thanks in advance</p>