CODEWITHSUNDEEP
mysql
Dustin Sun
Dustin Sun

Reputation: 5532

Help on a Complicated MySQL Query

Three Tables: User, Question and Answer

Table Answer has columns: AnswerID, UserID, QuestionID and Score.

a user can submit answer to a question more than once. But when ranking only the highest score he earned on the question should be counted.

Now I need get the rank (UserID, total points). How to write the query?

Thanks!

Upvotes: 1

Views: 103

Answers (4)

Vishal
Vishal

Reputation: 12369

But when ranking only the highest score he earned on the question should be counted.

Based on this what I understand is that you want the max score of each question for every user and then rank them

 SET @rank=0;
     SELECT 
          UserId,
          Sum(Points) as TotalPoints,
          Rank
        FROM
(
        SELECT UserID,MAX(Score) as Points,(@rank:=@rank+1) as Rank from Answer
        group by UserID,questionId
        order by  Points
) as d1
group by d1.UserId

Upvotes: 0

Brian Driscoll
Brian Driscoll

Reputation: 19635

I'm not sure of the full context of your query, but something like this should help:

SELECT QuestionID, UserID, MAX(Score) FROM Answer GROUP BY UserID, QuestionID

EDIT

Based on OP Comment, query should be more like this:

SELECT UserID, SUM(SELECT Max(Score) FROM Answer GROUP BY UserID, QuestionID) AS TotalScore GROUP BY UserID

To be honest I'm not sure if the subquery is 100% correct so you might not get the exact result you want, but the basic form of it is right.

Upvotes: 0

Mchl
Mchl

Reputation: 62359

First select highest scored answers, then calculate total points.

SELECT
  UserID, SUM(Score) AS TotalPoints  
FROM (
  SELECT
    UserID, QuestionID, MAX(Score) AS Score
  FROM
    Answer
  GROUP BY
    UserID, QuestionID
) AS sq
GROUP BY
  UserId

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85036

What about using a sub query like this:

SELECT UserId, SUM(
SELECT MAX(SCORE) FROM Answer a
WHERE a.uestionId = q.questionId)
FROM Question q
WHERE q.userId = 1234
Group by userid

Upvotes: 0

Related Questions