Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm sure my title is not clear enough...

Let's say I have a "chat" table, storing individual messages like this:

message_id, from_user_id, to_user_id, timestamp

For a given user_id, I want a list of "conversations" for this user. A conversation would be all messages involving the same 2 users. Kinda of like Facebook.

What would be the best way to achieve this? I was starting to write a simple query with GROUP BY but then realized the pair of user ids could be both ways - but to me it's still the same conversation.

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY ... ??
share|improve this question
I think you need a self join – Mihai Feb 2 '14 at 19:26
Do you want to count them or show all of them? In the second case, you don't want to use GROUP BY. – ypercubeᵀᴹ Feb 2 '14 at 20:39
I want show them (not individual messages, but conversations, sorted by latest activity) – nute Feb 2 '14 at 20:41
And how do you know which message belongs to which conversation? You mean all messages between two users constitute a conversation? – ypercubeᵀᴹ Feb 2 '14 at 20:48
yes @ypercube . – nute Feb 3 '14 at 7:44

You can use a UNION of two subqueries:

    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
  ( SELECT  to_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE from_user_id = 1
    GROUP BY to_user_id 
    SELECT  from_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE to_user_id = 1
    GROUP BY from_user_id 
  ) AS tmp 
    user_id ;

Two indexes, on (from_user_id, to_user_id, timestamp) and (to_user_id, from_user_id, timestamp) would help efficiency of the subqueries.

share|improve this answer
Thank you. It looks good, however I took a simpler approach by getting some help from php. I created a hash combining both user ids (…) and used that to identify each conversations. I added a conversation field, and was able to use a simple GROUP BY conversation. – nute Feb 3 '14 at 7:47

I know it's a late answer but I created this to do a similar thing in an application:

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY
CONCAT(GREATEST(from_id,to_id), '-', LEAST(from_id,to_id))

Hopefully this helps someone :)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.