MySQL: Get most current value in group by query

Question

I have two tables questions and answered. answered contains the answers for all the questions for all the users. One user can answer a question multiple times. A question can be answered correctly or incorrectly.

I am looking for a query that will return the count of correct and incorrect answers over all questions in one category. I want to use the most current answer only, though. So if a user answered the same question incorrectly before and correctly more recently, I only want to count the newest - correct - one.

This is what I got so far:

http://sqlfiddle.com/#!2/31e2e/2/0

SELECT a.correct, count(*) as count
FROM answered a JOIN questions q ON a.question_id = q.id 
WHERE a.user_id = 1 AND q.category_id = 1
GROUP BY correct

It returns

|  CORRECT  |  COUNT  |
-----------------------
|  0        |   2     |
-----------------------
|  1        |   4     |
-----------------------

What I want is

|  CORRECT  |  COUNT  |
-----------------------
|  0        |   1     |
-----------------------
|  1        |   2     |
-----------------------

Answer 1

Here is the query that you need:

SELECT a.correct, count(*) as counter
FROM answered a
JOIN (SELECT user_id, question_id, max(created) as maxCreated
      FROM answered
      GROUP BY user_id, question_id) aux
  ON a.user_id = aux.user_id AND
     a.question_id = aux.question_id AND
     a.created = aux.maxCreated
JOIN questions q ON a.question_id = q.id
WHERE a.user_id = 1 AND q.category_id = 1
GROUP BY a.correct

Use the aux sub-query to select only the rows with the last answer to a question from a given user.