Выбор студента с самой высокой оценкой из группы

#sql #sql_server

Есть таблица student(id_student, stname, averagemark, id_group). Задача - получить
по одному студенту из группы с наивысшей оценкой. Вот мой вариант:

SELECT s1.stname,
(SELECT max(averagemark) FROM student AS s2 WHERE s2.id_group = s1.id_group) AS averagemark,
s1.id_group
FROM    student AS s1
ORDER BY id_group;


Но он выводит всех студентов с самой высокой оценкой (т.е. если в таблице в группе
101 у пяти студентов самая высокая оценка - результат покажет всех пятерых. А нужен один)

Заранее спасибо!
    

Ответы

Ответ 1

Делим таблицу на партиции с помощью over и partition by, сортируя содержимое партиций
по убыванию оценок, т.о. образом в каждой партиции получаем максимальную оценку в первой
строке. Дальше выбираем из партиций все первые записи (where num = 1).

select stname, averagemark, id_group
from
(
    select *, row_number() over (partition by id_group order by averagemark desc) num
    from students
) parts
where num = 1