Господа! В процессе изучения SQL сам для себя придумал задачку, сам же её не смог решить. Задача была разобраться в группировке и агрегировании.

Задача звучит следующим образом:
В сформированной таблице хранятся данные об оценках студентов на экзаменах. Воображаемая экзаменационная сессия состоит из экзаменов по трём предметам - математика (math), физика (physics) и иностранный язык (language).
Каждый студент входит в одну из 4-х групп.
Таблица содержит поля:
student_name - имя студента
subject - название дисциплины
asmnt - оценка данного студента на экзамене по данной дисциплине
class_group - номер группы студента

Задача, на которой я заткнулся состоит в написании запроса на получение списка лучших (максимальная средняя оценка по всем предметам) студентов групп. Я всё это безобразие изучал в sqlite и его питонской библиотеке, поэтому у меня получилось решить задачу следующим запросом:

SELECT student_name, class_group, MAX(avasmnt)
FROM
(
SELECT student_name, class_group, AVG(asmnt) AS avasmnt
FROM assessments
GROUP BY student_name
)
GROUP BY class_group

Однако на собеседовании в одной конторке, глядя на такой код мне сказали, что sqlite это, конечно, хорошо, он такое сожрёт, но полноценные большие СУБД такое не примут - student_name не входит в список группировки, и не под функцией агрегирования. Мол, поставь дома хотя бы mysql и поработай там.
И вот что-то не складывается у меня. Вложенный запрос из приведённого получает среднюю оценку всех учеников. Далее, мне нужно сгруппировать полученные оценки по группам, выбрать максимум и... самое сложное - приклеить student_name. Я же во внешнем запросе ничего с ним не делаю?

Господа и дамы, подскажите, в какую сторону думать?

982183Может тебе ещё сказали, что class_group не входит в группировку и к нему не применена агрегирующая функция? (В подзапросе).
Не сказали, но, очевидно, в обоих SELECT-ах одна и та же ошибка. Мы оба это понимали.

Из вложенного запроса я могу убрать class_group, и он выдаст просто соответствие студент - средняя оценка. С конкретно этим SELECT-ом будет всё хорошо. Номер группы можно приклеить JOIN-ом. Тоже вроде не проблема.
Вопрос в том, как потом оных отгруппировать по class_goup с выводом максимума средней оценки, а вместе с ней имени и номера группы. Без имени студента - очевидно. Вопрос в том, как приклеить имя студента.

Если в группе несколько студентов, средняя оценка которых одинакова и больше других, то пускай для простоты принимается строка с любым одним из этих студентов. Не в этом на мой взгляд главная загвоздка.

982183Никто не мешает и добавить в группировку номера группы.

Если я добавляю вторую группировку, то результат получается аналогичным для "вложенного" запроса - просто студенты, номера групп и их средние оценки.

А вот метод "в лоб" я попробую, возможно он и есть ключ к успеху.