title: Get latest record from second table left joined to first table
body: <p>I have a candidate table say <code>candidates</code> having only <code>id</code> field and i left joined <code>profiles</code> table to it. Table <code>profiles</code> has 2 fields namely, <code>candidate_id</code> &amp; <code>name</code>. </p>
  
  <p>e.g. Table <code>candidates</code>: </p>
  
  <pre><code> id
   ----
   1
   2
  </code></pre>
  
  <p>and Table <code>profiles</code>: </p>
  
  <pre><code> candidate_id      name
   ----------------------------
        1           Foobar
        1           Foobar2
        2           Foobar3
  </code></pre>
  
  <p>i want the latest name of a candidate in a single query which is given below:</p>
  
  <pre><code>SELECT C.id, P.name 
    FROM   candidates C
  LEFT JOIN profiles P ON P.candidate_id = C.id 
  GROUP BY C.id 
  ORDER BY P.name;
  </code></pre>
  
  <p>But this query returns: </p>
  
  <pre><code>1     Foobar
  2     Foobar3
  </code></pre>
  
  <p>...Instead of:</p>
  
  <pre><code>1     Foobar2
  2     Foobar3
  </code></pre>