SQL-запрос - определение рейтинга пользователя в викторине
У меня есть таблица QuizAttempt в моей базе данных, как показано ниже
| QAID | QID | UID | Score | Time |
| ---- | --- | --- | ----- | ---- |
| 1 | 5 | 3 | 119 | 105 |
| 2 | 5 | 3 | 117 | 70 |
| 3 | 5 | 3 | 119 | 90 |
| 4 | 5 | 4 | 120 | 130 |
где QAID = идентификатор попытки викторины, QID = идентификатор теста, UID = идентификатор пользователя, Score = оценка в этой попытке и Time = время, затраченное в секундах.
Я хочу сначала получить ранги пользователя на основе оценки по убыванию, а если оценка такая же, то по времени по возрастанию. Также я должен сгруппировать пользователей, которые прошли тест более одного раза, и показать свои лучшие попытки.
Итак, результат должен быть таким (сгруппированный UID 3)
| QAID | QID | UID | Score | Time |
| ---- | --- | --- | ----- | ---- |
| 4 | 5 | 4 | 120 | 130 |
| 3 | 5 | 3 | 119 | 90 |
Я знаю, что могу применить Group By UID, а затем Max (Score), но дополнительно я должен выбрать Time также для этой записи, но могу ли я выбрать минимальный счет рядом с 119 в GROUP BY?
Потому что если я напишу
Select QID, UID, Max(Score) as Topper, Min(Time) as MinTime from QuizAttempt
Group By QID, UID
Order By Topper DESC
Я понял это
| QID | UID | Topper | MinTime |
| --- | --- | ------ | ------- |
| 5 | 4 | 120 | 130 |
| 5 | 3 | 119 | 70 |
Я получаю 70, что составляет общий мин (время) и принадлежит записи с Оценка 117
И если я попробую это
Select QID, UID, Max(Score) as Topper, Min(Time) as MinTime from QuizAttempt
Group By QID, UID,Score
Order By Topper DESC
я получил
| QID | UID | Topper | MinTime |
| --- | --- | ------ | ------- |
| 5 | 4 | 120 | 130 |
| 5 | 3 | 119 | 90 |
| 5 | 3 | 117 | 70 |
Как правильно сделать группировку? Я использую Студия управления MS SQL 2008
Ответы 1
Вы можете использовать row_number():
select qa.*
from (select qa.*,
row_number() over (partition by uid order by score desc, time asc) as seqnum
from QuizAttempt qa
) qa
where seqnum = 1;