title: Using sub query to group by specific column is outputting the same erroneous result
body: <p>I asked <a href="https://stackoverflow.com/questions/45372199/mysql-select-only-one-row-from-each-patient-diagnosed-according-the-the-first-da/45372533?noredirect=1">this question few days ago</a> on SO, but with no valid answer for it.</p>
  
  <p>The problem was that the following query:</p>
  
  <pre><code>SELECT t1.patient_id,
  CONVERT(aes_decrypt(t4.patient_name_en, :encKey) USING utf8mb4) as patient_name_en,
  min(t3.date_of_visit) as date_of_visit, 
  t2.diagnosis_name,
  max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
  ifnull(t5.assessment_result, 0) as assessment_result 
  FROM consultation t1
  LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
  LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
  LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
  LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
  WHERE t2.diagnosis_name LIKE :diagName AND t1.clinic_id = :cid
  AND t3.visit_status=:visit_status
  GROUP BY t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_result
  ORDER BY t5.date_of_assessment DESC 
  </code></pre>
  
  <p>Is giving the following result:</p>
  
  <p><a href="https://i.stack.imgur.com/3dtG2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3dtG2.png" alt="enter image description here"></a></p>
  
  <p>But what I really want is to group by only <code>patient_id</code> so I can get only one row per one patient who was diagnosed earlier with Diabetes.</p>
  
  <p>I searched a lot and found that I can use sub query inside this query.</p>
  
  <p>So I came up with the following sub query:</p>
  
  <pre><code>SELECT t1.patient_id
  FROM consultation t1
  LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
  LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
  LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
  LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
  WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
  AND t3.visit_status="Active"
  GROUP BY t1.patient_id
  </code></pre>
  
  <p>The result was grouped by <code>patient_id</code>, and I got the 2 id that really want and not more. Even the patient with ID <code>0361</code> was diagnosed with multiple types of diabetes in each visit to the clinic, but it's id is shown once.</p>
  
  <p>Now I want to take this sub query and add it to the initial one:</p>
  
  <pre><code>SELECT t1.patient_id,
  CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) as patient_name_en,
  min(t3.date_of_visit) as date_of_visit, 
  t2.diagnosis_name,
  max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
  ifnull(t5.assessment_result, 0) as assessment_result 
  FROM consultation t1
  LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
  LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
  LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
  LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
  WHERE t1.patient_id IN 
  (SELECT t1.patient_id
  FROM consultation t1
  LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
  LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
  LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
  LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
  WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
  AND t3.visit_status="Active"
  GROUP BY t1.patient_id) AND
  t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
  AND t3.visit_status="Active"
  GROUP BY t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_result
  ORDER BY t5.date_of_assessment DESC 
  </code></pre>
  
  <p>But I've got the same result of the image above, where per example <code>patient_id=0361</code> is shown in 4 rows, but I want is to be shown once, with the date of the first time he was diagnoised with Diabetes. </p>