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

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

Try This

WITH CTE
AS
(
SELECT
    SeqNo = ROW_NUMBER() OVER(ORDER BY COUNT(1) DESC),
    P.PersonId,
    Cnt = COUNT(1)
    FROM Person p
        INNER JOIN Classes C
            ON P.PersonId = C.PersonId
        GROUP BY p.PersonId
)
SELECT
    *
    FROM Person P2
        WHERE EXISTS
        (
            SELECT 1 FROM CTE WHERE PersonId = p2.PersonId AND SeqNo = 1
        )

T-SQL offers ranking functions that allow you to calculate the rank according to a field or aggregate.

Given these tables :

declare @people table (personid int primary key,name nvarchar(20))
declare @classes table (personid int ,class nvarchar(20))

insert into @people(personid,name)
values
(1,'Bob'),
(2,'Bill'),
(3,'Barbara'),
(4,'Bonnie'),
(5,'Joe')

insert into @classes (personid,class)
values
(1,'Math'),
(1,'Science'),
(2,'Math'),
(2,'English'),
(3,'Science'),
(3,'English'),
(4,'English'),
(4,'Math'),
(4,'Science')
(5,'Science')

The following query will calculate the person rank according to the number of classes taken:

select p.name,count(*) As Classes,rank() over (order by count(*) desc) as Rank
from @classes c
inner join @people p on p.personid=c.personid
group by p.name

Which returns Bonnie with 3 classes. All other students come second with 2 classes:

name    Classes Rank
------- ------- ----
Bonnie  3       1
Barbara 2       2
Bill    2       2
Bob     2       2
Joe     1       5

RANK will skip positions if there are ties. That's why Joe has a rank of 5.

You can't use a ranking function in a WHERE or HAVING clause. To return only the first student(s) you need to use a subquery or CTE, eg:

select name,classes
from (
    select p.name,count(*) As Classes,rank() over (order by count(*) desc) as Rank
    from @classes c
    inner join @people p on p.personid=c.personid
    group by p.name
) r
where rank=1

Or

;with r as (
    select p.name,count(*) As Classes,rank() over (order by count(*) desc) as Rank
    from @classes c
    inner join @people p on p.personid=c.personid
    group by p.name
)
select name,classes
from r
where rank=1

Both queries will return :

name    Classes
------- -------
Bonnie  3      

If you want to find the N best students, you should use DENSE_RANK and return rows where the rank is less than or equal to N. With DENSE_RANK Joe's rank would be 3.

The following query will return students in the first two places :

with r as (
    select p.name,count(*) As Classes,dense_rank() over (order by count(*) desc) as Rank
    from @classes c
    inner join @people p on p.personid=c.personid
    group by p.name
)
select name,classes
from r
where rank<=2

Get the person ID(s) with most classes with a TOP query. Then select the person's names from the people table:

select name 
from people
where people_id in
(
  select top(1) with ties person_id
  from classes
  group by person_id
  order by count(*) desc
);

Okay, I found the most effective way via the Having COUNT(*) >= ALL (Select..) command line.
Code as follows:
Select People.Name as 'Most Classes Taken:' from People inner join Classes On People.PersonID = Classes.PersonID Group by People.Name Having count(*)>=ALL (Select count(*) from Classes group by Classes.PersonID)