title: difficulty conceptualizing sql query
body: <p>I'm a bit jammed up on a current reporting issue that I'm hoping to find some help with.</p>
  
  <p>A doctor is retiring. Her patients need to be notified of her retirement. People who constitute as "her patient" have seen her more times than any other doctor in the past two years. Failing that (ie. has seen her and another doctor 2 times each in the past 2 years), they are "her patient" if they went to see her most recently.</p>
  
  <p>Here's what I have, so far.</p>
  
  <pre><code>select patient_id, last_name, first_name, post_fromdate, performing_md_id, performing_md_name, category_desc, procedure_code
  from [table]
  where post_fromdate &gt;= ADD_MONTHS(SYSDATE,-24)
  and category_desc like '%WELL%' --specific visit type
  and patient id in ([list of patients])
  group by patient_id, last_name, first_name, post_fromdate, performing_md_id, performing_md_name, category_desc, procedure_code
  order by patient_id, post_fromdate desc
  </code></pre>
  
  <p>To recap...</p>
  
  <p>What I have: a list of every doctor's visit every patient of the doctor in question, including visits to other doctors.</p>
  
  <p>What I seek: some additional criteria that will indicate if the retiring doctor has been visited the most by each individual patient. If seen equally but not less than the other doctors for a given patient, then if the retiring doctor was visited most recently.</p>
  
  <p>Any assistance is graciously accepted. Thank you in advance.</p>
  
  <p>EDIT: the desired result would be output that gives me the information in the select clause, with one and only one performing_md_id per patient_id indicating that patient's doctor (the doctor they have seen the most times in the past two years. If not that, then the doctor they have seen most recently).</p>