Kizspy.me
Question 7:
Picture 6.1
Write a query to display the most common diagnoses given by each doctor with the
corresponding distinct patients that the doctor has diagnosed with each most common
diagnosis. The results must include DoctorID, Doctor FullName, Diagnosis, PatientID,
PatientFullName; where Diagnosis is the most common diagnosis given by the doctor,
PatientID and PatientFullName are the information of patients that the doctor has diagnosed
with that most common diagnosis. Note that the PatientFullName is the concatenation of the
patient's first name, a white space and the patient's last name. The most common diagnosis
of each doctor is determined based on the number of medical records in which the doctor
has given the diagnosis. Order the results in ascending order of DoctorFullName, then in
ascending order of Diagnosis for rows of the same doctor, then in ascending order of
PatientID for rows of the same doctor and the same diagnosis. Note that a doctor may have
many most common diagnoses and he/she could give one diagnosis for many different
patients. For example, Dr. David Davis has two different most common diagnosis 'Allergy'
and 'Flu' and he has already diagnosed the 'Allergy' for two distinct patients and the 'Flu' for
two distinct patients as shown in the following figure. Note that the results have in total 83
rows, the following figure shows as example only the 26 first rows of the results.
Zoom
- FUO
+ 100%
Close
