group users by message last sent

Question

I have a table with messages sent from 1 user to another . I would like to know all messages sent or received from different users not the same .Ex: 1-2 is same as 2-1 but since 2 has made the last contact to user that message will be shown on top and also the name of the user to whom the message is sent or received from other than myself .ALso check if any message is sent from same user like 1-1 .I need to get the name too. I really appreciate any help.Thanks in Advance.

code

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

CREATE TABLE if not exists tblB
(
id int(11) NOT NULL auto_increment ,
sno varchar(255),
name varchar(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (sender, receiver,msg,date ) VALUES
('1', '2', 'buzz ...','2011-08-21 14:11:09'),
('1', '2', 'test ...','2011-08-21 14:12:19'),
('1', '2', 'check ...','2011-08-21 14:13:29'),
('1', '1', 'test2 ...','2011-08-21 14:14:09'),
('2', '1', 'check2 ...','2011-08-21 14:15:09'),
('2', '1', 'test3 ...','2011-08-21 14:16:09'),
('1', '2', 'buzz ...','2011-08-21 14:17:09'),
('1', '2', 'test ...','2011-08-21 14:18:19'),
('1', '2', 'check ...','2011-08-21 14:19:29'),
('1', '1', 'test2 ...','2011-08-21 14:10:09'),
('3', '1', 'check2 ...','2011-08-21 14:21:09'),
('3', '1', 'test3 ...','2011-08-21 14:22:09'),
('3', '2', 'buzz ...','2011-08-21 14:24:09'),
('3', '2', 'test ...','2011-08-21 14:25:19'),
('1', '3', 'check ...','2011-08-21 14:26:29'),
('1', '3', 'test2 ...','2011-08-21 14:27:09'),
('2', '3', 'check2 ...','2011-08-21 14:28:09'),
('2', '3', 'test3 ...','2011-08-21 14:29:09'),
('1', '2', 'check3 ...','2011-08-21 14:23:09');


INSERT INTO tblB (sno, name ) VALUES
('1', 'Aa'),
('2', 'Bb'),
('3', 'Cc');

I am looking on the lines of SMS inbox msgs where data comes/sent to different user and that name along with the last msg sent is displayed.How do I group that?

like this:

http://jesperbylund.com/wp-content/uploads//2011/11/Facebook-Messenger-459x900.png

---

I want the list of people Aa is talking to ordered by Date.

Bb
Cc

everything apart from Aa as he is the user.

Answer 1

I'm not sure to understand what you want, but try this:

SELECT t1.sender, t1.receiver, MAX(t1.date)
FROM tblA t1
INNER JOIN (SELECT * FROM tblA ) t2 ON t1.sender = t2.sender 
                                    AND t1.receiver = t2.receiver 
                                    AND t1.sender <> t2.receiver
 GROUP BY t1.sender, t1.receiver