MySQL Query to retrieve last entry in another table

Question

I have 2 mysql tables (Version Minor to MySql 8) in a simple chat app

Chat

id_chat	chat_name
1	My first chat
2	My Second Chat

And Chat_Message

id_chat_message	id_chat	message	date
1	1	How	03/Mar/2021
2	1	Are you	04/Mar/2021
3	2	This	05/Mar/2021
4	2	Is other	06/Mar/2021

How can I make a Query if I want to retrieve the last message for every chat?

The resultset should be

id_chat	chat_name	last_message	last_message_date
1	My first chat	Are you	04/Mar/2021
2	My Second Chat	Is other	06/Mar/2021

Thanks

Tim Biegeleisen · Answer 1 · 2021-03-16T06:27:18.860

0

On MySQL 8+, we can use ROW_NUMBER here:

WITH cte AS (
    SELECT c.id_chat, c.chat_name, cm.message, cm.date,
           ROW_NUMBER() OVER (PARTITION BY c.id_chat ORDER BY cm.date DESC) rn
    FROM Chat c
    INNER JOIN Chat_Message cm ON cm.id_chat = c.id_chat
)

SELECT id_chat, chat_name, message, date AS last_message_date
FROM cte
WHERE rn = 1;

On earlier versions of MySQL, we can join to a subquery which finds the latest chat message from the second table.

SELECT c.id_chat, c.chat_name, cm.message, cm.date AS last_message_date
FROM Chat c
INNER JOIN Chat_Message cm
    ON cm.id_chat = c.id_chat
INNER JOIN
(
    SELECT id_chat, MAX(date) AS max_date
    FROM Chat_Message
    GROUP BY id_chat
) t
    ON t.id_chat = cm.id_chat AND t.max_date = cm.date;

edited Mar 16 '21 at 06:27

answered Mar 16 '21 at 06:13

Tim Biegeleisen

502,043
27
286
360

Thanks, is an old server so MySQL 8+ is not supported – cbeltrangomez Mar 16 '21 at 06:24
@cbeltrangomez Thanks for the curve ball, I have updated my answer with a version which should work for you. – Tim Biegeleisen Mar 16 '21 at 06:28

score 0 · Answer 2 · answered Mar 16 '21 at 06:24

After some tests I came with this, but I'm not 100% sure it works in all cases:

SELECT hc.id_chat, hc.chat_name, hcm.message as last_message, hcm.date as last_message_date from chat hc inner join (SELECT a.* 
FROM chat_message a
INNER JOIN (
    SELECT id_chat, MAX(date) date
    FROM chat_message
    GROUP BY id_chat
) b ON a.id_chat = b.id_chat AND a.date = b.date) hcm on hc.id_chat = hcm.id_chat group by hcm.id_chat;

Some inspiration came from SQL select only rows with max value on a column

Nope, this is no good. And indicates that your using a version of MySQL prior to 5.7, which is really a bit out of date. :-( — Strawberry, Mar 16 '21 at 08:18

MySQL Query to retrieve last entry in another table

2 Answers2