Kizspy.me
Question 7:
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, DoctorFullName, Diagnosis, PatientID,
PatientFullName; where Diagnosis is the most common diagnosis given by the doctor,
PatientID and Patient FullName 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
Zoom
6 of 6
Paper No: 2
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.
Diagnosis PatientID PatientFullName
1234567
DoctorID
DoctorFullName
16
Dr. Chris Taylor
Arthritis 32
Laura Davis
16
Dr. Chris Taylor
Arthritis 46
Jane Wilson
1
Dr. David Davis
Allergy
41
John Davis
1
Dr. David Davis
Allergy
50
Laura Moore
1
Dr. David Davis
Flu
33
John Taylor
1
Dr. David Davis
Flu
67
Emma Taylor
221
21
Dr. David Miller
Asthma
19
Michael Miller
Jaka Tarden
+ 95%
Close
