Question

Which of the query below list the most expensive service received by each patient?

A. SELECT service, max(cost)
FROM services
GROUP BY service

B. SELECT patientID, max(cost)
FROM services
GROUP by service

C. SELECT patientID, service
FROM services
where cost =
(SELECT max(s.cost) as mc
FROM services s WHERE s.patientID = services.patientID)

D. SELECT patientID, service
FROM services
GROUP BY patientID
HAVING cost = mac(cost)

Answer 1

C
SELECT patientID, service
FROM services
where cost =
(SELECT max(s.cost) as mc
FROM services s WHERE s.patientID = services.patientID)