How to get latest messages for each user in mysql?

Question

I have database to store customer and messages

I am trying to get list of all the customer and their latest messages like first screen in messenger.

SELECT *
FROM message AS m
LEFT JOIN customer AS c ON c.id=m.sender_id
ORDER BY m.sent_at DESC

but this returns all the message for all user. I've also tried doing this

SELECT * 
FROM message AS m
LEFT JOIN customer AS c ON c.id=m.sender_id
GROUP BY c.id

but this doesn't run on all databases and cannot sort result set to get latest messages only.

Answer 1

One option uses row_number(), available in MySQL 8.0:

select *    -- better enumerate the columns you want here
from customer as c
left join (
    select m.*, row_number() over(partition by m.sender_id order by sent_at desc) rn
    from messages m
) m on on c.id = m.sender_id and m.rn = 1
order by m.sent_at desc

This gives you the last message per customer. You can change the condition on rn if you want more messages (rn <= 3 would give you three messages per customer).

Note that I changed the order of the tables in the left join, so it allows customers without messages (rather than messages without customers, which probably does not make sense).

If you are running an earlier version, than an alternative is to filter with a subquery:

select *    -- better enumerate the columns you want here
from customer as c
left join messages m 
    on  m.sender_id = c.id
    and sent_at = (select min(m1.sent_at) from messages m1 where m1.sender_id = m.sender_id)

For perforamnce with the correlated subquery, consider an index on (sender_id, sent_at) (ideally, there should be no duplicates in these columns).