Question 20180 in Mysql Question posted in
The official MySQL documentation can be found here.

how to select conversation list with one last message with one query – Mysql

i have table conversation

conversation_id name is_group
1 Global chat 1
2 private chat 0

and i have table messages

message_id conversation_id sender_id text created_at
1 1 1 hello 06-04-2023 14:00:00
2 1 1 whatsup 06-04-2023 14:01:00
3 2 1 hello 06-04-2023 14:50:00
4 2 1 how are you? 06-04-2023 14:51:00

i need to get with one query conversation list with last message, something like this:

conversation_id name is_group sender_id last_message_text created_at
1 Global chat 1 1 whatsup 06-04-2023 14:01:00
2 private chat 0 1 how are you? 06-04-2023 14:51:00

i tried with two query

  1. to get all chat list
  2. to get last message of chat throught loop
Tags:

2

Answers


  1. 0

    You can use the row_number window function to assign a running number for messages per conversation, and then join on the last one:

    SELECT c.*. m.sender_id, m.text AS last_message_text, m.created_at
FROM   conversation c
JOIN   (SELECT conversation_id, sender_id, text, created_at,
               ROW_NUMBER() OVER (PARTITION BY conversation_id 
                                  ORDER BY created_at DESC) AS rn
        FROM   messages) m ON c.conversation_id = m.conversation_id AND rn = 1
    Login or Signup to reply.
  2. 0

    You can do it using inner join to join with a set of max created_at per conversation as follows :

    select c.*, m.sender_id, m.text_ as last_message_text, m.created_at
from conversation c
inner join messages m on m.conversation_id = c.conversation_id
inner join (
  select conversation_id, max(created_at) as max_created_at
  from messages m
  group by conversation_id
) s on s.conversation_id = m.conversation_id and s.max_created_at = m.created_at

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search