CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

group users by last sent/received message

I have a tbl:

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
sender varchar(255),
receiver varchar(255),
 msg varchar(255),
 date timestamp,
  idmsg int(11)  ,
name varchar(255),
 PRIMARY KEY (id)
);




INSERT INTO tblA (sender, receiver,msg,date,name,idmsg ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09','Bb','1'),
('1', '2', 'test ...','2011-08-21 14:12:19','Bb','2'),
('1', '2', 'check ...','2011-08-21 14:13:29','Bb','3'),
('1', '1', 'test2 ...','2011-08-21 14:14:09','Aa','4'),
('2', '1', 'check2 ...','2011-08-21 14:15:09','Bb','5'),
('2', '1', 'test3 ...','2011-08-21 14:16:09','Bb','6'),
('1', '2', 'buzz ...','2011-08-21 14:17:09','Bb','7'),
('1', '2', 'test ...','2011-08-21 14:18:19','Bb','8'),
('1', '2', 'check ...','2011-08-21 14:19:29','Bb','9'),
('1', '1', 'test2 ...','2011-08-21 14:10:09','Aa','10'),
('3', '1', 'check2 ...','2011-08-21 14:21:09','Cc','11'),
('3', '1', 'test3 ...','2011-08-21 14:22:09','Cc','12'),
('1', '3', 'check ...','2011-08-21 14:26:29','Cc','13'),
('1', '3', 'test2 ...','2011-08-21 14:27:09','Cc','14'),
('1', '2', 'check3 ...','2011-08-21 14:23:09','Bb','15'),
('1', '4', 'test2 ...','2011-08-21 14:27:09','Dd','16'),
('1', '5', 'test2 ...','2011-08-21 14:27:09','Ee','17'),
('1', '7', 'test3 ...','2011-08-21 14:29:09','Gg','18');

How to get distinct group with sender, receiver if 1,2 is same as 2,1 with max(date). I have tried http://www.sqlfiddle.com/#!2/b04cd5/29 but I am not getting the exact grouping.

I get 14:11:+09 for 1,2 or 2,1 combo but the right answer is 14:23. Also it's not checking for reverse like its checking for 1,2 but not for 2,1. How Do I fix this?

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to want the latest communication between two people. I think this does what you want:

select least(receiver, sender), greatest(receiver, sender),
       max(date),
       substring_index(group_concat(msg order by date separator '|'), '|', 1) as msg,
       substring_index(group_concat(idmsg order by date), ',', 1) as idmsg,
       substring_index(group_concat(name order by date separator '|'), '|', 1) as name
from tblA 
where '1' in (Receiver, Sender)
group by least(receiver, sender), greatest(receiver, sender);

Upvotes: 1

Related Questions