SQL查找每组的最高记录(SQL finding the top record per group)

SQL查找每组的最高记录(SQL finding the top record per group)

有点卡在查询上。 我有一个测验结果表，其中包含：

ID of the player, QuizID, Number of seconds it took to complete the quiz, Number of correct answers

我怎样才能找到每个测验的胜利者。 =具有最大正确答案数的玩家和每个QuizID的最小秒数

我尝试了很多方法，但我无法得到正确的结果。 希望有人可以帮忙。

谢谢！

Kind of stuck on a query. I have a table of quiz results which contains:

ID of the player, QuizID, Number of seconds it took to complete the quiz, Number of correct answers

How would I find the winner of every quiz. = the player with the max number of correct answers AND minimum number of seconds for every QuizID

I have tried many ways and I just can't get the correct results. Hope someone can help out.

Thanks!

最满意答案

CTE将为测验中的每个玩家计算位置。 主查询仅过滤第一个位置。

; with quiz as ( select QuizId, PlayerID, row_number() over (partition by QuizId order by count_answers desc, seconds) rownum from QuizResults ) select * from QuizResults inner join Quiz on QuizResults.QuizID = Quiz.QuizID and QuizResults.PlayerID = Quiz.PlayerID where rownum = 1

CTE will calculate place for each player in the quiz. main query filters first positions only.

; with quiz as ( select QuizId, PlayerID, row_number() over (partition by QuizId order by count_answers desc, seconds) rownum from QuizResults ) select * from QuizResults inner join Quiz on QuizResults.QuizID = Quiz.QuizID and QuizResults.PlayerID = Quiz.PlayerID where rownum = 1