How to Get most current value in group by query

PROBLEM

---

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.

SOLUTION

---

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.