How to write a query that retrieves 3 replies for each user in my users table?

Admin 1
Viewed 51

i want to display three replies from each user i have in my users table, so for instance if i have 3 users and each of them had replied to lets say 10 messages, i want my query to only retrieve 9 replies and not all of the replies in my messages_reply table.

heres what i tried: 

     $replyquery="select * 
     from messages_reply 
     LEFT JOIN users 
     ON messages_reply.from_id=users.id 
     GROUP BY messages_reply.id LIMIT 3";

i know that what i wrote means that bring me 3 replies only, so how do i bring 3 replies from each user in my users table?

greatest-n-per-group join mysql sql
2 Answers

In many databases, you can use row_number() for this:

select *
from (
    select mr.*, u.*, row_number() over(partition by u.id order by mr.id desc) rn
    from messages_reply mr
    inner join users u on mr.from_id = u.id 
) t
where rn <= 3

If you are running MySQL < 8.0, as I suspect from the lax use of group by in your query:

select mr.*, u.*
from messages_reply mr
inner join users u on mr.from_id = u.id 
where mr.id >= (
    select mr1.id
    from messages_reply mr1
    where mr1.from_id = u.id 
    order by mr1.id desc
    limit 2, 1
)

This gives you the 3 message replies with the greatest id for each user.

user_106767160

Query not tested, just a concept

SELECT *
FROM users
LEFT JOIN (
    SELECT *
    FROM messages_reply
    WHERE from_id = users.id
    ORDER BY <your wanted order field> DESC
    LIMIT 3) replies
    ON users.id = replies.from_id
user_85328920
Related