CODEWITHSUNDEEP
mysqlsql
hllktlhndd
hllktlhndd

Reputation: 303

Two columns unique combine

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.

Upvotes: 3

Views: 110

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can do something like this:

select m.*
from messages m
where m.message_id = (select max(m2.message_id)
                      from messages m2
                      where (m2.sender_id, m2.receipient_id) in ( (m.sender_id, m.receipient_id), (m.receipient_id, m.sender_id) )
                     ) and
       677 in (m.sender_id, m.receipient_id);

Upvotes: 0

Natig Gurbanov
Natig Gurbanov

Reputation: 36

create table #message
(message_id int null,
sender_id int null,
recipient_id int null,
message nchar(10) null )

insert into #message(message_id,sender_id,recipient_id,message)
values
(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');

SELECT message_id,sender_id,recipient_id,message
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY sender_id,recipient_id ORDER BY sender_id DESC) 'RowDistinctNumber'
      FROM #message
     )sub 
WHERE RowDistinctNumber = 1

message_id  sender_id   recipient_id message
----------- ----------- ------------ ----------
7           677         678          message 1 
10          677         681          message 3 
11          677         698          message 4 
12          678         677          message 5 

(4 row(s) affected)

Upvotes: 0

Ramesh
Ramesh

Reputation: 1484

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

Upvotes: 3

SS_DBA
SS_DBA

Reputation: 2423

Give this a try... I'm not MySQL, but I think this will work for you.

Select Max(Message_ID), Sender_ID, Recipient_ID, Max(Message)
From Table
Where Sender_ID = 677 OR Recipient_ID = 677
Group By Sender_ID, Recipient_ID

Upvotes: 0

Related Questions