title: MySQL select MAX date from joined table
body: <p>I have this <code>MySQL</code> query:</p>
  
  <pre><code>SELECT 
      s.student_id,
      s.student_firstname,
      s.student_lastname,
      sd.student_startdate,
      sd.student_enddate,
      s.isActive,
      c.city_name,
      ctc.category_name
  FROM
      students s
          INNER JOIN
      (SELECT sd1.student_id, sd1.student_startdate, sd1.student_enddate FROM studentdates sd1) sd ON sd.student_id = s.student_id
          INNER JOIN
      cityselections c ON c.city_id = s.student_city_id
          INNER JOIN
      coursecategory ctc ON s.student_course_category_id = ctc.category_id
  WHERE
      sd.student_enddate BETWEEN CURDATE() AND ADDDATE(CURDATE(), INTERVAL 14 DAY)
          AND s.student_city_id NOT LIKE '1'
          AND s.student_city_id = 18
          AND s.isActive = 1
  GROUP BY s.student_id
  ORDER BY sd.student_enddate ASC , s.student_lastname , s.student_firstname
  </code></pre>
  
  <p>In the table <code>studentdates</code> there can be multiple <code>student_startdate</code> and <code>student_enddate</code> on each <code>student_id</code> but I want to select each students <code>MAX(student_enddate)</code>.</p>
  
  <p>How can this be done?</p>