Mysql Message application list latest 5 messages from each user

Question

Table strucutre

    CREATE TABLE IF NOT EXISTS `mail_box` (
      `msg_id` int(11) NOT NULL AUTO_INCREMENT,
      `sender_id` int(11) NOT NULL,
      `receiver_id` int(11) NOT NULL,
      `message` text NOT NULL,
      `date` timestamp NOT NULL,
      `attachment` varchar(255) NOT NULL,
      `status` tinyint(1) NOT NULL,
      PRIMARY KEY (`msg_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

    CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Need the query for listing the latest 5 messages from each user.

Query I tried for Dashboard listing

SELECT usr.name as Receiver,usr1.name as Sender, message,date
FROM mail_box 
JOIN users as usr on usr.id = receiver_id 
JOIN users as usr1 on usr1.id = sender_id 
ORDER BY date DESC
LIMIT 5

This query is limiting my result to 5 not showing the latest 5 messages from each user.

[Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results), [How to select the first/least/max row per group in SQL](https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/) — Solarflare, Mar 16 '17 at 12:58

score 0 · Answer 1 · answered Mar 16 '17 at 13:43

Standard SQL has window function and it will be very easy to solve this but mysql does not support window function yet. Using a special feature of myisam table in auto_increment should do the trick:

DROP TEMPORARY TABLE IF EXISTS _tmp_user;
CREATE TEMPORARY TABLE IF NOT EXISTS _tmp_user(sender_id int not null, send_order int not null auto_increment, msg_id int not null, primary key (sender_id,send_order), unique key (msg_id)) ENGINE=MYISAM;

INSERT _tmp_user(sender_id,send_order,msg_id)
SELECT m.sender_id,NULL,m.msg_id
FROM mail_box m
ORDER BY `date`
DESC;

SELECT usr.name as Receiver,usr1.name as Sender, message,date
FROM _tmp_user t
INNER JOIN mail_box m
ON t.msg_id=m.msg_id
JOIN users as usr on usr.id = m.receiver_id 
JOIN users as usr1 on usr1.id = m.sender_id 
WHERE t.send_order<=5;

can you please explain its logic – Syam K Prakash Mar 17 '17 at 09:09 — Syam K Prakash, Mar 17 '17 at 09:09

Mysql Message application list latest 5 messages from each user

Query I tried for Dashboard listing

1 Answers1