facebook messages result set like

Question

I'm trying to get my head around with the group by command, basically I'm trying to select all messages of a user, group them by subject then show them - just like how face book does it.

The result should have the latest message id, the sender's id, the date, and the total count of the messages in that subject.

The message table can look like the following:

message
-------
id
sender_id
subject
created_at

So the result should show all user's messages grouped by the same subject.

Answer 1

SELECT sender_ID, subject, MAX(id), MAX(created_at), COUNT (id) 
FROM message 
WHERE recipient_id = current_user_id OR sender_id = current_user_id
GROUP BY subject, sender_ID, receipient_id
ORDER BY MAX(created_at) DESC

With GROUP BY, anything that isn't in the GROUP BY clause needs to be in an aggregate funtion (e.g. COUNT, SUM, MAX, etc) in the SELECT statement.

EDIT - realised the Group By above doesn't quite do what I think you want (altho example result set would help), so one thought to simplify this. At the time of message creation are you able to set an additional column value - conversation_id. This would be assigned to a new value when a new message is created and then reused for any replies. You could then Group By conversation_id to group the messages together in the way that I think you'd like them to be