MySQL使用RIGHT JOIN获取单条最新消息数据的问题排查及正确实现问询

Your existing RIGHT JOIN is on the right track to include all phone numbers from the chats table (even those without a matching contact), but it doesn't filter for the latest message per phone number. Instead, it just returns all chat records sorted by time, which is why you're not getting the single latest entry for each number.

Window functions like ROW_NUMBER() are perfect for this scenario—they let you rank records within each group (here, each phone number) by their creation time, then pick only the top-ranked (latest) entry.

WITH ranked_chats AS (
    SELECT 
        phone_number,
        messages,
        created_at,
        -- Assign rank 1 to the latest message per phone number
        ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY created_at DESC) AS rn
    FROM chats
)
SELECT 
    rc.phone_number,
    c.name,
    rc.messages
FROM ranked_chats rc
-- Left join to keep numbers without a matching contact (name will be NULL)
LEFT JOIN contacts c ON rc.phone_number = c.phone_number
WHERE rn = 1; -- Only keep the latest message per number

If you're working with an older SQL dialect that doesn't support window functions, you can use a subquery to first find the latest created_at timestamp for each phone number, then join back to the chats table to get the corresponding message.

SELECT 
    c.phone_number,
    con.name,
    c.messages
FROM chats c
LEFT JOIN contacts con ON c.phone_number = con.phone_number
-- Join with subquery that gets the latest time per phone number
INNER JOIN (
    SELECT phone_number, MAX(created_at) AS latest_time
    FROM chats
    GROUP BY phone_number
) latest ON c.phone_number = latest.phone_number AND c.created_at = latest.latest_time;

Both queries will return exactly the result you're looking for:

内容的提问来源于stack exchange，提问作者Tko Code