title: Joining tables based on the maximum value
body: <p>Here's a simplified example of what I'm talking about:</p>
  
  <pre><code>Table: students      exam_results
  _____________       ____________________________________
  | id | name |       | id | student_id | score |   date |
  |----+------|       |----+------------+-------+--------|
  |  1 | Jim  |       |  1 |          1 |    73 | 8/1/09 | 
  |  2 | Joe  |       |  2 |          1 |    67 | 9/2/09 |
  |  3 | Jay  |       |  3 |          1 |    93 | 1/3/09 |
  |____|______|       |  4 |          2 |    27 | 4/9/09 |
                      |  5 |          2 |    17 | 8/9/09 |
                      |  6 |          3 |   100 | 1/6/09 |
                      |____|____________|_______|________|
  </code></pre>
  
  <p>Assume, for the sake of this question, that every student has at least one exam result recorded.</p>
  
  <p><strong>How would you select each student along with their highest score?</strong> <em>Edit</em>: ...AND the other fields in that record?</p>
  
  <p>Expected output:</p>
  
  <pre><code>_________________________
  | name | score |   date |
  |------+-------|--------|
  |  Jim |    93 | 1/3/09 |
  |  Joe |    27 | 4/9/09 |
  |  Jay |   100 | 1/6/09 |
  |______|_______|________|
  </code></pre>
  
  <p>Answers using all types of DBMS are welcome.</p>