SQL Server: Select entry based on frequency of entries in another table

Question

So I am using SQL Server and I have two tables similar to the ones below:

People
Person ID    Name
1                  Bob
2                  Bill
3                  Barbara
4                  Bonnie

Classes
Person ID    Class
1                  Math
1                  Science
2                  Math
2                  English
3                  Science
3                  English
4                  English
4                  Math
4                  Science

I need to write a query that returns the name, and only the name, of the person taking the most classes. So the only result after running the query for the case above should be the name 'Bonnie'.

In the case of a tie, multiple names should be returned.

My attempt as follows:

`Select People.Name
from People inner join Classes 
On People.PersonID = Classes.PersonID
Group by People.Name
Having max(Classes.PersonID)`

The last line does not work in SQL Server, and I can't for the life of me figure out how to re-word the code to make it functional.

Any thoughts?

Answer 1

SELECT  TOP 1
        name
FROM    (
            SELECT  name,
                    COUNT(p.PersonID) as cnt
            FROM    People p
                    JOIN Classes c
                        ON p.PersonID = c.PersonID
            GROUP BY name
        ) a
ORDER BY cnt DESC