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.

Edit
Report