I have a list of blog posts, people can leave comments and I reply to them

This is how the structure looks like

table structure

Is there a way, with a single query, to get all posts where I'm not the last person to leave a comment?

Ideally I would want something like this

SELECT *
FROM posts
INNER JOIN comments
WHERE # I'm not the last person to leave a comment

Here is an online example : https://onecompiler.com/mysql/3zsb5urne I'm using MySQL 8 but I don't know if it matters.

These are some of the things I tried (among many, many others...)

SELECT *
FROM comments
ORDER BY created_at desc
WHERE post_id = 1
LIMIT 1

I also tried this

SELECT user_id, max(created_at)
FROM comments
GROUP BY post_id

but it doesn't work because this is incompatible with sql_mode=only_full_group_by

(not sure if this is a valid query in the 1st place)

However I'm not sure how to combine/join this to with the posts table so that it limits the results for each post.

CodePudding user response：

You could use the ROW_NUMBER() window function like this:

SELECT *
FROM posts p
JOIN (
    SELECT post_id, user_id,
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_at DESC) rn
    FROM comments
) c ON p.id = c.post_id AND c.rn = 1 AND c.user_id <> 1;

Or you could use some subqueries like this:

SELECT *
FROM posts
WHERE id IN (
    SELECT post_id
    FROM comments
    WHERE (post_id, created_at) IN (
        SELECT post_id, MAX(created_at)
        FROM comments
        GROUP BY post_id
    )
    AND user_id <> 1
);

Here's a db<>fiddle.

Note: neither of these show posts that do not have any comments.