consult in mariadb, get the last message of each user

Question

I am doing a chat and I need to make a query to my database in which I bring the last message that a user received from each person with whom he wrote, the query should be based on the date, the last message according to the date

the table would be the following

id   id_destination   message   sender     date
1          1123          hi      1133    18/06/2019
2          1123          bye     1133    17/06/2019
3          1123          ok      1144    18/06/2019
4          1123          hi      1144    17/06/2019

I need that once executed the query will show me in the following way:

id   id_destination   message   sender     date
1          1123          hi      1133    18/06/2019
3          1123          ok      1144    18/06/2019

I am reverting your latest edit. We very much prefer text to images in questions. — O. Jones, Jun 18 '19 at 18:04

score 0 · Answer 1 · answered Jun 18 '19 at 17:58

0

try by using dense_rank()

slect * from 
(select *,dense_rank()over(partition by id_destination order by date desc) rn
from table
) a where rn=1

answered Jun 18 '19 at 17:58

Zaynul Abadin Tuhin

31,407
5
33
63

O. Jones · Answer 2 · 2019-06-18T18:16:52.653

Find the message with the highest id for each destination and sender, using this subquery.

                          SELECT MAX(id) id
                            FROM messages
                        GROUP BY id_destination, sender

Then use it to select messages, like this: (https://www.db-fiddle.com/f/9W7YTSN8Dq7TuRYnL1jmXE/0)

   SELECT *
     FROM messages
    WHERE id IN (
                          SELECT MAX(id) id
                            FROM messages
                        GROUP BY id_destination, sender
                )

You may append AND sender = something or any other filter to this.

This assumes each new message gets an autoincrementing ID value, so later messages have higher ID values than earlier ones.

Using the dates is a little trickier, and a lot slower. (https://www.db-fiddle.com/f/6MKc7At6MKPm98eUEPKMYz/0) . It also has a potential ambiguity, if multiple messages occur on the latest date.

SELECT * 
FROM messages m
JOIN (
                      SELECT MAX(date) date, id_destination, sender
                        FROM messages
                    GROUP BY id_destination, sender
     ) x ON m.date=x.date 
        AND m.id_destination = x.id_destination
        AND m.sender = x.sender

consult in mariadb, get the last message of each user

2 Answers2