enter image description here

I need to return a result of the Student Name who is enrolled in the most sections from the Is_Registered_T table.

What is the easiest way to do this? I have used an INTO function below to create a separate table, and I can return the most records (4) but not the name of the student (Aiken). Is there a simpler solution?

Select Student_Name, Count(Section_ID) Sections_Enrolled
from Student_t, IS_Registered_t
where Student_t.Student_ID = IS_Registered_t.Student_ID
group by Student_Name

Student_Name Sections_Enrolled
------------ -----------------
Agnes        2
Aiken        4
Altvater     3
Dianne       1
Eddie        1
Kayla        1
LeterSky     2
Marra        2
Michael      3

CodePudding user response：

Depending on your use-case, you could:

1. Get all students with the number of enrollments equal to the maximum number of enrollments (i.e. if there are multiple students which enrolled 4 times, you would return all of them)
2. Order students by the number of enrollments and take the first one (if multiple students have the same number of enrollments, an arbitrary student would be chosen)

Solution 1.1 (preferred)

Uses Top with ties and produces a simpler execution plan

SELECT TOP 1 WITH TIES s.Student_Name, COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID, s.Student_Name
ORDER BY COUNT(*) DESC

Solution 1.2 - "manual"

Not preferred, produces a more complex execution plan. The innermost query counts the sections enrolled; the one-upper-level query finds the maximum number of the sections enrolled; the outmost query once more counts the sections enrolled, and filters the result to include only those which are equal to the previously found max number

SELECT s.Student_Name, COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID, s.Student_Name
HAVING COUNT(*) = -- number of sections enrolled equals to the maximum number of sections enrolled
          (SELECT MAX(c.Sections_Enrolled)
          FROM (SELECT COUNT(*) AS Sections_Enrolled
               FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
               GROUP BY s.Student_ID) c)

Solution 2

SELECT TOP 1 s.Student_Name, COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID, s.Student_Name
ORDER BY COUNT(*) DESC

If you just want the name, you can remove the COUNT(*) AS Sections_Enrolled from the outmost query.

DbFiddle

CodePudding user response：

SELECT
    MIN(s.Student_Name) AS Name,
    COUNT(*) AS Sections_Enrolled
FROM Student s INNER JOIN 
    Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM Is_Registered
    GROUP BY Student_ID
);

Or extra succinct:

with summary as (select Student_ID, count(*) cnt from Is_Registered group by Student_ID)
select (select Student_Name from Students where Student_ID = s.Student_ID) as Student_Name, cnt
from summary s where cnt = (select max(cnt) from summary);