MAX(... help)

[lehmann]

I cant get this function to perform how is it suppoed to be

SELECT MAX(SELECT Sum(Prescription.Quantity) gross
FROM Doctor, Prescription
WHERE Doctor.DoctorID = Prescription.PHN
GROUP BY Doctor.Name, Prescription.PCoName
HAVING (Prescription.PCoName="MedCorp&quot);

 

[Tim1]

Hiya,

A having clause should only be used for aggregate functions, such as checking counts, mins, maxes etc.

Try :

SELECT MAX(SELECT Sum(Prescription.Quantity) gross
FROM Doctor, Prescription
WHERE Doctor.DoctorID = Prescription.PHN
AND (Prescription.PCoName="MedCorp&quot
GROUP BY Doctor.Name, Prescription.PCoName);

Hopefully that should give you what you are looking for.

HTH

Tim