How to make a request to display the latest messages in the room

Question

I am doing chat.I have 4 tables:

Rooms

Messages

Guest

Users

In the messages table messages are stored, in the table Users - users, Guest - stores visitors in the room. A room is like a dialogue or conversation. I want to make a request to get the latest message for each room where a particular user is a guest.

SELECT DISTINCT TOP (15) r.id_room, r.type_room, r.name_room,
    m.content_message, m.image_message,
    m.file_message, m.date_message, u.person
    FROM Rooms AS r
      INNER JOIN Guest AS g ON g.id_room = r.id_room
      INNER JOIN Users AS u ON u.id_user = g.id_user
      INNER JOIN Messages AS m ON m.id_room = r.id_room
        WHERE g.id_user = 'OCI59TNG8ZZ'
        ORDER BY m.date_message DESC

And he displays all the messages that were inside the room

I think this request would be good if id_room were not duplicated. But I don’t understand how to do it

It would be better to show DDL of your tables instead of pictures. — Max Zolotenko, Dec 27 '19 at 14:58
Does this answer your question? [How do I use ROW\_NUMBER()?](https://stackoverflow.com/questions/961007/how-do-i-use-row-number) — Amira Bedhiafi, Dec 27 '19 at 15:50

score 0 · Answer 1 · answered Dec 27 '19 at 14:51

You can try this:

SELECT  id_room,
        type_room,
        name_room,
        content_message,
        image_message,
        file_message,
        date_message,
        person
FROM    (
            SELECT  r.id_room,
                    r.type_room,
                    r.name_room,
                    m.content_message,
                    m.image_message,
                    m.file_message,
                    m.date_message,
                    u.person,
                    ROW_NUMBER() OVER(PARTITION BY R.ID_ROOM ORDER BY m.date_message DESC)  AS  RN
            FROM    Rooms AS r
            INNER JOIN Guest AS g ON g.id_room = r.id_room
            INNER JOIN Users AS u ON u.id_user = g.id_user
            INNER JOIN Messages AS m ON m.id_room = r.id_room
            WHERE   g.id_user = 'OCI59TNG8ZZ'
        ) V1
WHERE   V1.RN = 1

score 0 · Accepted Answer · edited Apr 13 '20 at 15:12

0

Use a Common Table Expression like below :

WITH CTE AS (
            SELECT  r.id_room,
                    r.type_room,
                    r.name_room,
                    m.content_message,
                    m.image_message,
                    m.file_message,
                    m.date_message,
                    u.person,
                    ROW_NUMBER() OVER(PARTITION BY R.ID_ROOM ORDER BY m.date_message DESC)  rn
            FROM  Rooms AS r
            INNER JOIN Guest AS g ON g.id_room = r.id_room
            INNER JOIN Users AS u ON u.id_user = g.id_user
            INNER JOIN Messages AS m ON m.id_room = r.id_room
            WHERE   g.id_user Like 'OCI59TNG8ZZ'
        )

SELECT * FROM CTE WHERE rn = 1

edited Apr 13 '20 at 15:12

answered Dec 27 '19 at 14:55

Amira Bedhiafi

8,088
6
24
60

Have you forgotten 'DESC' in the 'OVER' clause? – Max Zolotenko Dec 27 '19 at 15:02
ORDER BY m.date_message for some reason does not work in this answer... – твой кот Dec 27 '19 at 15:05
@твойкот just added DESC in the OVER clause. It should be working – Amira Bedhiafi Dec 27 '19 at 15:50

How to make a request to display the latest messages in the room

2 Answers2