I am writing a query to count how many students each staff member supervises
SELECT FACULTY.F_FIRST || ' ' || F_LAST AS STAFF,(COUNT(FACULTY.F_FIRST || ' ' || F_LAST)) as STUDENTCOUNT
FROM STUDENT
INNER JOIN FACULTY
ON STUDENT.F_ID=FACULTY.F_ID
GROUP BY FACULTY.F_FIRST,FACULTY.F_LAST
;
which correctly outputs
Teresa Marx 3
Colin Langley 1
Joel Brown 1
Mark Zhulin 1
I now want to modify it so the query would only show the rows where the staff supervises the most students including multiple staff members e.g if two staff had 3 students they would both show
I thought I would be able to do it through a HAVING clause of
HAVING ((COUNT(FACULTY.F_FIRST || ' ' || F_LAST)))
= MAX(COUNT(FACULTY.F_FIRST || ' ' || F_LAST))
but this tells me i have a invalid relational operator in it.I believe the first part of the have clause is correct since i can use it to find specific row values but not the maximum