Select department/s name/s with the highest number of highest grades-CodePudding

I am having 3 tables - student, department and exam_results. What I am trying to do here is to select all of the department/s name/s which have the highest grade for example 6.

I have tried the following query but in this example's case where we have 2 departments with grade 6 but the Informatics one has 2 max grades and Chemistry has only 1 I am still retrieving the Chemistry department name which should not be returned but only the Chemistry department name(also if we have 2 max grades for Chemistry we should be able to retrieve both Informatics and Chemistry records).

SELECT department
FROM (SELECT d.department_name as department, count(e_r.grade) as cnt
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE e_r.grade = 6
      GROUP BY d.department_name
     ) as ex;

Also with the following query I was able to accomplish to retrieve the department/s name/s and the count of the given 'n' grade in the WHERE clause but for some reason I am not able to accomplish what I really want.

SELECT department_name, max(cnt) as cnt
FROM (SELECT d.department_name as department_name, e_r.grade, count(e_r.grade) as cnt
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE grade = 6
      GROUP BY d.department_name, e_r.grade
     ) AS ex
GROUP BY department_name;

Providing a dbfiddle link with the explained example: https://dbfiddle.uk/siEjfNXP

Would be glad to receive any suggestions or hints on how to achieve this, thank you!

Here are the tables with example values(all included in dbfiddle link):

Student table:

student_id	department_id
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5

Department table:

department_id	department_name
1	Informatics
2	Biology
3	Physics
4	Geography
5	Chemistry

Exam_results table:

student_id	grade
1	6
2	6
3	4
4	4
5	3
6	3
7	2
8	2
9	6
10	5

CodePudding user response：

In case you want a mire flexible solitution if the ighest ranks are not 6

WITH CTE as
  (SELECT 
  d.department_name as department_name,e_r.grade, count(e_r.grade) as cnt
    , DENSE_RANK() OVER (ORDER BY e_r.grade DESC ,count(e_r.grade) DESC) rk
FROM  exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
  WHERE (d.department_name,e_r.grade) IN
(select d.department_name as department_name, MAX(e_r.grade) 
      from exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id     
      group by d.department_name)
group by d.department_name, e_r.grade)
SELECT department_name,grade,cnt FROM CTE WHERE rk = 1

department_name	grade	cnt
Informatics	6	2

fiddle

CodePudding user response：

Given you want only one row (the department with most biggest grade), you can order by your count of grades descendently, then limit rows to 1.

SELECT d.department_name as department
FROM exam_results e_r
INNER JOIN students s ON e_r.student_id = s.student_id
INNER JOIN department d ON s.department_id = d.department_id
WHERE e_r.grade = 6
GROUP BY d.department_name
ORDER BY COUNT(e_r.grade) DESC
LIMIT 1

Check the demo here.

If you're allowing more than one max value, you can use the DENSE_RANK window function to assign a ranking order to your counts, then get all the records that have denserank = 1.

SELECT department
FROM (SELECT d.department_name as department,
             DENSE_RANK() OVER(ORDER BY COUNT(e_r.grade) DESC) AS rn
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE e_r.grade = 6
      GROUP BY d.department_name
     ) AS ex
WHERE rn = 1

Check the demo here.