title: SQL Multiple inner joins with max() for latest recorded entry
body: <p>Attempting to build SQL with INNER JOIN's. The INNER JOIN's work ok, now I need to add the MAX() function for limiting the rows to just most recent. Added this INNER JOIN client_diagnosis_record ON SELECT cr.PATID, cr.date_of_diagnosis, cr.most_recent_diagnosis...
  Received this SQL code error, need some help, I'm sure it a simple oversight but my eyes are getting dim from looking so long...</p>
  
  <p>Syntax error: [SQLCODE: &lt;-4>:
  
  <pre><code>SQLCODE: &lt;-4&gt;:&lt;A term expected, beginning with one of the following: identifier, constant, aggregate, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %
  
  [%msg: &lt; The SELECT list of the subquery
  
  
  SELECT pd.patient_name,
         cr.PATID,
         cr.date_of_diagnosis,
         cr.EPISODE_NUMBER,
         ce.diagnosing_clinician_value, 
         ce.data_entry_user_name,
         most_recent_diagnosis
  FROM client_diagnosis_record cr 
  INNER JOIN patient_current_demographics pd ON cr.patid = pd.patid 
  INNER JOIN client_diagnosis_entry ce ON ce.patid = pd.patid
                                      AND cr.ID = ce.DiagnosisRecord
  INNER JOIN client_diagnosis_record ON (SELECT cr.PATID,
                                                cr.date_of_diagnosis,
                                                cr.most_recent_diagnosis
                                         FROM ( SELECT patid,
                                                       date_of_diagnosis,
                                                       MAX(ID) AS most_recent_diagnosis
                                                FROM client_diagnosis_record) cr
   INNER JOIN RADplus_users ru ON ru.staff_member_id = ce.diagnosing_clinician_code
   WHERE cr.PATID &lt;&gt; '1'
   AND ce.diagnosis_status_value ='Active'
   AND (ru.user_description LIKE '%SOA' OR ru.user_description LIKE '%OA')
   GROUP BY cr.PATID
  </code></pre>