SQL - Displaying entries that are the max of a count?

Question

CREATE TABLE doctor( patient CHAR(13), docname CHAR(30) );

Say I had a table like this, then how would I display the names of the doctors that have the most patients? Like if the most was three and two doctors had three patients then I would display both of their names.

This would get the max patients:

SELECT MAX(count) 
FROM (SELECT COUNT(docname) FROM doctor GROUP BY docname) a;

This is all the doctors and how many patients they have:

SELECT docname, COUNT(docname) FROM doctor GROUP BY name;

Now I can't figure out how to combine them to list only the names of doctors who have the max patients.

Thanks.

Answer 1

This should do it.

SELECT docname, COUNT(*) FROM doctor GROUP BY name HAVING COUNT(*) =      (SELECT MAX(c) FROM         (SELECT COUNT(patient) AS c          FROM doctor          GROUP BY docname)) 

On the other hand if you require only the first entry, then

SELECT docname, COUNT(docname) FROM doctor  GROUP BY name  ORDER BY COUNT(docname) DESC LIMIT 1; 

Answer 2

This should do it for you:

SELECT docname FROM doctor GROUP BY docname HAVING COUNT(patient)=     (SELECT MAX(patientcount) FROM         (SELECT docname,COUNT(patient) AS patientcount          FROM doctor          GROUP BY docname) t1) 

Answer 3

Here's another alternative that only has one subquery instead of two:

SELECT docname
FROM author
GROUP BY name
HAVING COUNT(*) = (
    SELECT COUNT(*) AS c
    FROM author
    GROUP BY name
    ORDER BY c DESC
    LIMIT 1
)