CODEWITHSUNDEEP
sql-server
gen
gen

Reputation: 405

SQL finding the top record per group

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!

Upvotes: 0

Views: 102

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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

Upvotes: 4

juergen d
juergen d

Reputation: 204746

Unverified:

select ID 
from your_table 
group by QuizId
having max(count_answers) and min(seconds)

Upvotes: 0

Related Questions