MySQL advenced GROUP BY statement

I have table of messages in mysql. Columns are named as sender, reciever, text, id, date.

Query like this:

SELECT max(id), text 
FROM `messages` 
WHERE reciever = $user_id OR sender = $user_id 
GROUP BY reciever, sender 
ORDER BY date DESC

It groups messages by sender (or reciever) and it returns last message id max(id) but it does not return last message text.

Is there any way to solve this problem without开发者_Go百科 JOIN and UNION?

Thanks a lot!

You can do this as follows:

SELECT * FROM messages 
WHERE id IN 
    (
         SELECT MAX(id) FROM messages
         WHERE reciever = $user_id OR sender = $user_id
         GROUP BY reciever, sender
    ) 
ORDER BY date DESC

Group-by operation can ONLY be used in companion with aggregation operations like sum, average and count. Using other attributes just makes no sense.

Imagine you have two tuples with the same reiceiver, what would you expect the grouped-by text to be ? It just makes no sense.

According my guess for your objective, the answer from @danishgoel may be what you want, if I have guessed wright.

To select last message of $user_id:

SELECT id, text FROM `messages`
WHERE `reciever` = $user_id OR `sender` = $user_id 
ORDER BY `id` DESC
LIMIT 1