Reputation: 303
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
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
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
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
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