Sélectionner le(s) nom(s) du/des département(s) ayant le plus grand nombre de notes les plus élevées

Question

J'ai 3 tables - étudiant, département et résultats d'examen. Ce que j'essaie de faire ici, c'est de sélectionner tous les noms de départements qui ont la note la plus élevée, par exemple 6.

J'ai essayé la requête suivante mais dans le cas de cet exemple où nous avons 2 départements avec le grade 6 mais l'informatique a 2 grades maximum et la chimie n'en a qu'un, je récupère toujours le nom du département de chimie qui ne devrait pas être retourné mais seulement le nom du département de chimie (de plus si nous avons 2 grades maximum pour la chimie nous devrions pouvoir récupérer les enregistrements de l'informatique et de la chimie).

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;

Avec la requête suivante, j'ai pu récupérer les noms des départements et le nombre de grades donnés dans la clause WHERE, mais pour une raison ou une autre, je ne parviens pas à obtenir ce que je veux vraiment.

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;

Fournir un lien dbfiddle avec l'exemple expliqué : https://dbfiddle.uk/siEjfNXP

Je serais heureux de recevoir des suggestions ou des conseils sur la manière d'y parvenir, merci !

Voici les tableaux avec des exemples de valeurs (tous inclus dans le lien dbfiddle) :

Table des élèves :

numéro d'étudiant

département_id

1

1

2

1

3

2

4

2

5

3

6

3

7

4

8

4

9

5

10

5

Tableau des départements :

département_id

nom_du_département

1

Informatique

2

Biologie

3

Physique

4

Géographie

5

Chimie

Tableau des résultats d'examen :

numéro d'étudiant

grade

1

6

2

6

3

4

4

4

5

3

6

3

7

2

8

2

9

6

10

5

Answer 1

Au cas où vous souhaiteriez une solution plus souple si les rangs les plus élevés ne sont pas 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

nom_du_département

grade

cnt

Informatique

6

2

violon

Answer 2

Si vous ne voulez qu'une seule ligne (le département avec la plus grande note), vous pouvez ordonner votre nombre de notes de façon descendante, puis limiter les lignes à 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

Vérifier la démo aquí .

---

Si vous autorisez plus d'une valeur maximale, vous pouvez utiliser la fonction DENSE_RANK pour attribuer un ordre de classement à vos comptages, puis obtenir tous les enregistrements dont le 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

Vérifier la démo aquí .