2

I have database to store customer and messages

enter image description here

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Kshitij Dhakal
  • 816
  • 12
  • 24
  • 1
    provide sample data and your desired output in table format – Fahmi Sep 07 '20 at 13:04
  • What if some user have no messages? does this user must be returned with NULL or he must not be returned at all? You use LEFT join - does you have messages which belongs to non-existent users? – Akina Sep 07 '20 at 13:15
  • If users don't have messages should not return. I should be using Inner Join it was mistake is question. – Kshitij Dhakal Sep 07 '20 at 13:28

1 Answers1

5

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).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Interesting answer! You able to explain what is happening here `over(partition...`? I have never seen this. – Eduards Sep 07 '20 at 13:09
  • @LV98: `row_number()` is a window function; it ranks recodrds having the same sender by descending sent time. You can read more about window functions [in the documentation](https://dev.mysql.com/doc/refman/8.0/en/window-functions.html). – GMB Sep 07 '20 at 13:15
  • @GMB it gave error like ```#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by m.sender_account_id order by m.sent_at desc ) rn from rtc_messa' at line 7.``` mysql version = 5.7.31-0ubuntu0.18.04.1 – Kshitij Dhakal Sep 07 '20 at 13:54
  • @KshitijDhakal: I updated my answer with a solution for earlier versions. – GMB Sep 07 '20 at 14:06