☑Kizspy.me
oyomanry
Picture 6.1
Question 7:
Write a query to display the most common medicines given by each doctor with the
corresponding distinct patients that the doctor has prescribed with each most common
medicine. The results must include DoctorID, DoctorFullName, Medicine, PatientID,
PatientFullName; where Medicine is the most common medicine given by the doctor,
PatientID and PatientFullName are the information of patients that the doctor has prescribed
with that most common medicine. 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 medicine
of each doctor is determined based on the number of prescriptions in which the doctor has
prescribed the medicine. Order the results in ascending order of Doctor FullName, then in
ascending order of Medicine for rows of the same doctor, then in ascending order of
PatientID for rows of the same doctor and the same medicine. Note that a doctor may have
many most common medicines and he/she could give one medicine for many different
patients. For example, Dr. Emilly Williams has three different most common medicines,
'Amoxicillin', 'Metformin' and 'Paracetamol' and she has already prescribed each of these
medicines for three distinct patients as shown in the following figure. Note that the results
have in total 105 rows, the following figure shows as example only the 29 first rows of the
results.
Zoom
- FUO
+ 100%
Close
