选择每个成员的最后一条消息

Question

I want to display the last message from each user. I wrote this MySQL request but it displays the last message from one user, and the first messages from other users. I don't understand why.

SELECT *
FROM cbadminmessages
   INNER JOIN cbmembres ON cbadminmessages.id_membre_message=cbmembres.id
WHERE id!='$admin_id'
AND id_message IN(
    SELECT MAX(id_message)
    FROM cbadminmessages
    GROUP BY id_membre_message)
ORDER BY id_message");

id_message is the message's id. id_membre_message is the member's ID who wrote the message.

Thank you.

Answer 1

Your subquery seems to be incorrect. You should refer to the outer tables to select the maximum for each specific user. Also, using table aliases will help a great deal.

SELECT ad.id_membre_message, ad.id_message #and anything else you'd like to select
FROM cbadminmessages ad
INNER JOIN cbmembres mem
ON ad.id_membre_message=mem.id
WHERE mem.id!='$admin_id'
AND ad.id_message = (
    SELECT MAX(ad_sub.id_message)
    FROM cbadminmessages ad_sub
    WHERE ad_sub.id_membre_message = mem.id)
ORDER BY ad.id_message");