[RESOLVED] Quiz: Select Winner...?

[komlos]

Hi,

I'm making a quiz script, and would like to select winner from the answer table the most efficient way, using only SQL query if possible.

The winner is the user with the most correct answers. In case of more users with the same number of correct answer, the winner will be te user with the lesser wrong answers.

I have the following table structure:

answer_id---user_id---quiz_id---question_id---correct---incorrect
1-----------34--------1---------1-------------1---------0--------
2-----------34--------1---------2-------------1---------0--------
3-----------34--------1---------3-------------0---------1--------
4-----------34--------1---------3-------------0---------1--------
5-----------35--------1---------1-------------0---------1--------
6-----------35--------1---------1-------------1---------0--------
5-----------35--------1---------2-------------1---------0--------

in the above example user 34 and 35 have the same correct answers (2) but user 35 has lesser (only 1) incorrect answers, so he is the winner.

I've tried using min max sql functions but didnt work the way I wanted.

Thanks

[Piranha]

I think this shouild work. Note that I often print "order by" and "group by" in the wrong order.

select userid 
from table 
where quizid = 1
order by sum(correct) desc, sum(incorrect) asc
group by userid, quizid
limit 0, 1

[komlos]

Thank you for your solution! First it didn't work, but led me the right direction. I had to tweak it a bit, now it works like this:

SELECT userid, quizid, SUM(correct) as correct, SUM(incorrect) as incorrect 
FROM inbox 
WHERE quizid = 1 
GROUP BY userid, quizid 
ORDER BY correct DESC, incorrect ASC 
LIMIT 0, 1