How to select the latest version of answers given by each answerer in MySQL?

Question

There is a scenario. There is an ask-and-answer website. An answerer can modify his answer, and the history of modification is saved on the server. Be default, only the latest version of each answer is displayed.

select * from answers where questionid='$questionid' group by answerer_id

So I can group all answers by answerer, then I need to select the latest version of each subgroup. How to achieve this?

Answer 1

If you are using autoincrement ids, you could select based on the highest id.

Possible SQL:

select * from answers where questionid='$questionid' 
    and id in (select max(id) from answers group by answerer_id)

Answer 2

The GROUP BY clause seems to just use the first available row, so you could try using a sub-query to re-arrange them so the latest answer is on top.

SELECT * 
FROM (
    SELECT * FROM `answers`
    WHERE questionid='$questionid'
    ORDER BY answerer_id DESC
) as `dyn_answers` 
GROUP BY answerer_id

Answer 3

Do a self-join and find the user/question with no higher id:

SELECT a.*
FROM answers AS a
    LEFT JOIN answers AS b
    ON a.answerer_id = b.answerer_id
        AND a.question_id = b.question_id
        AND a.id < b.id
WHERE
    b.id IS NULL

Or, if you have a timestamp you can use that.