Question 277729 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get only blog posts where I'm not the last commenter

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.

Tags:

2

Answers


  1. 0 
    select * 
from posts p
    , (select post_id, max(created_at) maxdt from comments group by post_id ) all
    , (select post_id, max(created_at) maxdt from comments where user_id = uid group by post_id ) my
where p.post_id = all.post_id
and p.post_id = my.post_id
and all.maxdt != my.maxdt
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search