How to get all latest messages but with limit to one for every user message

Question

I have been searching a lot, but probably I don't know how to ask the question properly.

I'm storing all chat messages between all users in table called 'user_messages' which looks like this:

message_from_user_id | message_to_user_id | message_content | message_date
                   1 |                  2 | "hey"           |       07:36
                   1 |                  2 | "how are u?"    |       07:37
                   2 |                  1 | "hey, im fine"  |       07:38
                   3 |                  1 | "wassup"        |       09:21
                   4 |                  2 | "wow"           |       11:55
                   5 |                  1 | "example"       |        5:34

Now let's say I'm the user with id 1. And I want to display my latest chats with all people (something like messenger).

The result I would like to achieve is:

message_from_user_id | message_to_user_id | message_content | message_date
                   3 |                  1 | "wassup"        |       09:21
                   2 |                  1 | "hey, im fine"  |       07:38
                   5 |                  1 | "example"       |        5:34

So basically I need to select all messages where message_from_user_id = 1 or message_to_user_id = 1 but how can I make that the only one latest result for every chat will be displayed?

Even in this scenario:

message_from_user_id | message_to_user_id | message_content | message_date
                   1 |                  2 | "hey"           |       07:36
                   1 |                  2 | "how are u?"    |       07:37
                   2 |                  1 | "hey, im fine"  |       07:38

I want to get only one result that would be this:

               2 |                  1 | "hey, im fine"  |       07:38

I'm using MySQL and PHP.

What MySQL version you use? – Slava Rozhnev Jun 15 '21 at 10:28 — Slava Rozhnev, Jun 15 '21 at 10:28

score 2 · Accepted Answer · answered Jun 15 '21 at 10:30

One method uses window functions:

select um.*
from (select um.*,
             row_number() over (partition by  coalesce(nullif(message_from_user_id, 1), message_to_user_id)
                                order by message_date desc
                               ) as seqnum
      from user_messages um
      where 1 in (message_from_user_id, message_to_user_id)
     ) um
where seqnum = 1;

The expression:

coalesce(nullif(message_from_user_id, 1), message_to_user_id)

Is just a more concise way of writing:

(case when message_from_user_id <> 1
      then message_to_user_id
      else message_from_user_id
 end)

How to get all latest messages but with limit to one for every user message

1 Answers1