Question 21250 in Mysql Question posted in
The official MySQL documentation can be found here.

Select department/s name/s with the highest number of highest grades – Mysql

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search