Group by reversible pair

You can use a UNION of two subqueries:
SELECT 
    user_id,
    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
FROM
  ( 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 
  UNION ALL
    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 
GROUP BY 
    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.

Comments

Popular posts from this blog

HOW TO REGISTER FOR SAFETOKEN