在数百名申请人中获取前3名记录

Question

i am developing an online test system in php,mysql..when a student submits a test its marks are calculated on the basis of correct answers matched from database using following query..

SELECT qa.question_id, qa.test_id, uta.user_answer,uta.user_id,  qa.type,
   qa.answers correct_answer,
 CASE WHEN uta.user_answer = qa.answers THEN 'correct' ELSE 'incorrect' END
 AS count(status)
 FROM questions_answer qa
 LEFT JOIN 
  (
  SELECT user_id, type, test_id, question_id,
  GROUP_CONCAT(answers ORDER BY answers) AS user_answer,
  timestamp from user_test_answers
  WHERE  test_id = '1'    
 GROUP BY user_id, question_id
  ) uta
 ON qa.question_id = uta.question_id
 where qa.test_id=1 GROUP BY uta.user_id

my problem is: I want to count the no of times "correct" appear in field STATUS field in table... these no of correct answers are passed as an argument in a function.... i cant get results of other students....so i want to compare on the basis of CORRECT string appearing in field on the basis of user_id and display top 3 scorers.. How to do this "top 3 scorers in a particular test"..

I am a newbie to MySQL..

Answer 1

If I had to guess, the following should give you what you want. Change:

 CASE WHEN uta.user_answer = qa.answers THEN 'correct' ELSE 'incorrect' END AS count(status)

to

sum(uta.user_answer = qa.answers) as NumCorrect