user3441417 user3441417 - 6 months ago 8
SQL Question

Querying to show all rows with a max value SQL

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
Jonnel 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

Answer

You can do this using DENSE_RANK() or RANK(). Here is an example:

SELECT (f.F_FIRST || ' ' || f.F_LAST) AS STAFF, COUNT(*) as STUDENTCOUNT
FROM FACULTY f INNER JOIN
     (SELECT s.F_ID, COUNT(*) as CNT,
             DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM STUDENT s
      GROUP BY s.F_ID
     ) s
     ON s.F_ID = f.F_ID
WHERE seqnum = 1;

This formulation assumes that the first/last name is unique for a given F_ID.

Also, note that the query is easier to write and to read when you use table aliases.