Getting 10 latests post for the 10 latest comments-CodePudding

I have a simple yet a mind bending MySQL scenario I am struggling with for the past couple of days.

Here is the table

Id | post_id | comment  | extra
1  | 800     | comment_1| foo_text
2  | 801     | comment_1| foo_text
3  | 801     | comment_1| foo_text
4  | 802     | comment_1| foo_text
5  | 802     | comment_1| foo_text
6  | 802     | comment_1| foo_text
7  | 803     | comment_1| foo_text

I need to get the 10 latest rows for 10 unique post_ids. ie. The final output should be 10 comments where each comment should belong to a unique post.

This is my attempt.

SELECT distinct post_id, id, comment
from comments
order by id desc limit 12)

The problem with the above query is, it has duplicate post_id. The problem sounds very simple but I cannot seem to understand why it's wrong. Can someone help me?

PS: I am using MySQL8 and my comments table has more than 5 million records.

CodePudding user response：

You can try to use ROW_NUMBER window function

SELECT *
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY post_id ORDER BY id DESC) rn
    FROM comments
) t1
WHERE rn = 1
LIMIT 10

CodePudding user response：

First select the post_id and then the comments

SELECT 
    post_id, id, comment
FROM
    comments
WHERE
    post_id IN (SELECT DISTINCT
            post_id
        FROM
            comments
        ORDER BY id DESC
        LIMIT 10)