MYSQL: Limiting rows per whereIn()

Question

users Table

id

user_comments Table

id | user_id | content | created_at

I have a list of user IDs, and I want to grab the latest 3 comments for each user id.

SELECT * FROM user_comments WHERE user_id IN (1, 2, 3, 4, 5) 
ORDER BY created_at DESC
LIMIT 3;

This will grab the last 3 comments from all matching IDs, I want the last 3 comments for each ID. 1 query without unions preferred.

I have tried right joining the table on itself but I cant seem to get it right.

** Edit: I cannot rely on the id column for ordering, it must use the date column.

Thanks.

** My Final Solution

SELECT user_comments.* FROM user_comments
LEFT OUTER JOIN user_comments user_comments_2
ON user_comments.post_id = user_comments_2.post_id 
    AND user_comments.id < user_comments_2.id    
where user_comments.post_id in (x,x,x) 
GROUP BY user_comments.id 
HAVING COUNT(*) < 3 
ORDER BY user_id, created_at

The answer proposed by @PaulSpiegel did work for me (with caveat), however I ended up going with the above join solution that I made using info from this thread: link

mentioned by Bill Karwin.

Thanks everyone!

SELECT * FROM user_comments WHERE user_id=1 OR user_id=2 OR user_id=3 OR user_id=4 ORDER BY created_at DESC LIMIT 3; — Álvaro Touzón, Feb 08 '17 at 21:35
@ÁlvaroTouzón - I have hundreds of user ids in some cases, ideally I wouldn't have to dynamically generate the query like that. — magnito, Feb 08 '17 at 21:39
http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category/1442867#1442867 — Bill Karwin, Feb 08 '17 at 22:21
@BillKarwin - I was able to get it working with proper date ordering using that thread. Thanks! — magnito, Feb 08 '17 at 23:49

Paul Spiegel · Accepted Answer · 2017-02-08T23:33:59.567

2

If you can use id instead of created_at, you can compare the id with the 3rd highest id per user. Which you can find in a subquery with LIMIT 1 OFFSET 2. For the case that a user has less than 3 comments use COALESCE (or IFNULL) to select all comments with id >= 0.

SELECT * 
FROM user_comments c
WHERE user_id IN (1, 2, 3, 4, 5)
  AND id >= COALESCE((
    SELECT id
    FROM user_comments c1
    WHERE c1.user_id = c.user_id
    ORDER BY id DESC
    LIMIT 1
    OFFSET 2
), 0)
ORDER BY user_id, id DESC

If you can not use id for ordering..

SELECT * 
FROM user_comments c
WHERE user_id IN (1, 2, 3, 4, 5)
  AND created_at >= COALESCE((
    SELECT created_at
    FROM user_comments c1
    WHERE c1.user_id = c.user_id
    ORDER BY created_at DESC
    LIMIT 1
    OFFSET 2
), '1970-01-01 00:00:00')
ORDER BY user_id, created_at DESC

Note that you then might (though unlikely) get more than 3 comments, if the 3rd and the 4th comment have the same timestamp.

edited Feb 08 '17 at 23:33

answered Feb 08 '17 at 21:40

Paul Spiegel

30,925
5
44
53

Unfortunately I cannot rely on the id column for ordering the comments since in my application they can be saved as a draft, and published at a later date. – magnito Feb 08 '17 at 23:14
@magnito That also works using a timestamp - but read the note. I've also added the missing ORDER BY clause in the subquery. – Paul Spiegel Feb 08 '17 at 23:38
I was able to get this mostly working, however I ended up going with the join solution proposed in the thread that BillKarwin mentioned. It doesnt have the same issue with duplicate dates and is faster (in my situation). – magnito Feb 08 '17 at 23:51

Ivan Gritsenko · Answer 2 · 2017-02-08T22:17:29.683

Try

select * 
from (
    select *, 
        @currentRank := if(@prevId = user_id, @currentRank, 0) + 1 as rank, 
        @prevId := user_id
    from user_comments
    order by user_id, created_at desc) as user_comments 
where rank <= 3

Inner query uses SQL @ variables that change the value from row to row. Comments of a particular user will be grouped together because of order by user_id. @currentRank variable will be storing row rank in a particular group. @currentRank will be zeroed out when new group starts.

The result is quit optimal as it needs RDMS to iterate over each record of user_comments table only once. However the outer where clause will be executed afterwards.

MYSQL: Limiting rows per whereIn()

2 Answers2

Linked