Cant you use the TOP N clause? see BOL
From: Jake Chapman [mailto:jakech@n...]
Sent: 04 March 2002 01:59
To: sql language
Subject: [sql_language] simple sql syntax problem
I'm trying to return the name of a doctor and the number of patients he or
she has. I'm trying to include only those doctors that have more than 20
patients. I've tried the following but can't get it to work:
SELECT Doctors.Name, COUNT(*) as count
FROM Doctors INNER JOIN Patients ON Doctors.DocID = Patients.DocID
WHERE count > 20
GROUP BY Doctors.Name
I'm sure the problem is 'WHERE' because the statement works without it.
Can someone tell the correct way to write this?
NOTICE AND DISCLAIMER:
This email (including attachments) is confidential. If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents. We cannot accept liability for any breaches of
confidence arising through use of email. Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions. We will not accept responsibility for any commitments
made by our employees outside the scope of our business. We do not warrant
the accuracy or completeness of such information.