title: LEFT JOIN on Max Value
body: <p>Suppose I have the following two tables:</p>
  
  <pre><code>STUDENT
  studentid   lastname   firstname
  1           Smith      John
  2           Drew       Nancy
  
  STUDENT_STORY
  studentid   dateline   storyid   status
  1           1328313600 10        2
  1           1328313601 9         1
  2           1328313602 14        2
  2           1328313603 12        1
  </code></pre>
  
  <p>Now, I need an SQL query that would select each student along with the latest story for that student in the student story table.</p>
  
  <p>I am trying this:</p>
  
  <pre><code>SELECT s.*, ss.*
  FROM student AS s
  LEFT JOIN (
      SELECT *
      FROM student_story
      WHERE student_story.studentid = s.studentid
      ORDER BY dateline DESC LIMIT 1
  ) AS ss ON (ss.studentid = s.studentid)
  </code></pre>
  
  <p>However, this query does not work. It complains about s.studentid being an unknown field in the where clause of the sub query.</p>
  
  <p>Please suggest how I can achieve what I'm trying to do.</p>
  
  <p>Thanks.</p>