Retrieve latest model belonging to a set of users from a MySQL table-CodePudding

I need to retrieve the latest model in a relationship, from a collection of records that belong to a set of users. The best I've come up with is:

SELECT 
    `answers`.*,
    answers.created_at,
    `questions`.`survey_id` AS `laravel_through_key`
FROM
    `answers`
        INNER JOIN
    `questions` ON `questions`.`id` = `answers`.`question_id`
WHERE
    `questions`.`id` IN (4, 5, 6)
        AND `user_id` IN (1 , 2, 3)
group by user_id, question_id
ORDER BY `created_at` DESC

The tables are:

questions

id, text

answers

id, user_id (belongs to a user), question_id (belongs to a question)

users

id, name

For a set of users with IDs 1, 2, 3 - I want to retrieve the set of answers to questions with IDs 4, 5, 6 - but I only want each user's most recent answer for each question. In other words, there should only be a single answer for each user/question combination. I thought using GROUP_BY would do the trick, but I don't get the most recent answer. I'm using Laravel, but the issue is more a SQL one rather than a Laravel specific problem.

CodePudding user response：

In MySQL 8 or later you can use window functions to find greatest n per group:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, question_id ORDER BY created_at DESC) AS rn
    FROM answers
    WHERE user_id IN (1 , 2, 3) AND question_id IN (4, 5, 6)
)
SELECT *
FROM cte
WHERE rn = 1