通过COUNT聚合分组出临时结果集，再从结果集中找出COUNT值最大的一行，该如何写SQL

Question

我想先通过count函数以及group by家庭作业ID、学生ID统计出每个学生做的每个家庭作业错误的总数量。然后根据总数量，得到每个学生错误数量最多的那一条记录。下面这个SQL，查出来的数据不准确。这样的SQL，该如何写？感谢

SELECT MAX(o.total),o.homework_id,o.student_id FROM (
     SELECT COUNT(id) AS total,homework_id,student_id FROM `homework_wrong_practices` WHERE `date` = 201911 AND `week` = 48 GROUP BY homework_id, student_id
) AS o GROUP BY o.student_id;

Answer 1

在子查询中加个排序规则

order by total desc

homework_id 会取子查询中各 student_id 对应的第一条记录的 homework_id，
所以我们先帮子查询排逆序就能保证拿到的是最大 total 对应的 homework_id

Answer 2

已找到解决方法

SELECT MAX(o.num) AS total,o.student_id,SUBSTRING_INDEX(GROUP_CONCAT(o.homework_id ORDER BY o.num DESC), ',', 1) as homework_id
 FROM (
    SELECT COUNT(id) AS num,homework_id,student_id FROM `homework_wrong_practices` WHERE `date` = 201911 AND `week` = 48 GROUP BY homework_id, student_id
) AS o GROUP BY o.studnet_id