Two columns unique combine

Question

i have table like this

message_id | sender_id | recipient_id | message
6            677         681            message 0
7            677         678            message 1
9            677         678            message 2
10           677         681            message 3
11           677         698            message 4
12           678         677            message 5

i want to group sender_id and recipient_id unique between them with only one parameter. For example i want to select rows which relevant with user_id 677 table like this;

message_id | sender_id | recipient_id | message
10           677         681            message 3
11           677         698            message 4
12           678         677            message 5

I have been working on it for hours and I did not get the desired results.

Answer 1

Here is a solution

SELECT * 
  FROM message
 WHERE message_id 
    IN (SELECT MAX(message_id)
          FROM message
          GROUP BY CASE WHEN sender_id < recipient_id 
                        THEN CONCAT(CONCAT(sender_id, ','), recipient_id)
                        ELSE CONCAT(CONCAT(recipient_id, ','), sender_id)
                    END)
ORDER BY message_id   ;

It just gets a combined key between sender_id and recipient_id to find the distinct records using group by.

SQL Fiddle Demo here - http://sqlfiddle.com/#!9/9df65c/11

Hope this helps