Nanji Mange Nanji Mange - 4 months ago 9
MySQL Question

How to get 5 record of each group with order by descending using mysql?

I want to get 5 emails of every account of

Inbox
folder from "Mails" Table
Table contain field of MailAccountID.

Table details:
Table Name: Mails
Folder field: FolderName
Email Account field: MailAccountID


SELECT * FROM `Mails` WHERE `FolderName` = "Inbox" GROUP BY `MailAccountID` ORDER BY `Date` DESC limit 5


Above query return only 5 mails. Can anybody suggest me?

Answer
/*
create table mails(id int,mailaccountid int,foldername varchar(6),dt date);
truncate table mails;
insert into mails values
(1,1,'inbox','2016-08-01'),
(2,1,'inbox','2016-08-02'),
(3,1,'inbox','2016-08-03'),
(4,2,'outbox','2016-08-01'),
(5,2,'inbox','2016-08-02'),
(6,2,'inbox','2016-08-03'),
(7,3,'inbox','2016-08-01'),
(8,3,'outbox','2016-08-02'),
(9,3,'inbox','2016-08-03'),
(10,4,'inbox','2016-08-03'),
(10,4,'inbox','2016-08-03'),
(10,4,'inbox','2016-08-03'),
(10,4,'inbox','2016-08-04'),
(10,4,'inbox','2016-08-05')
;
*/
select * from
(
select m.*,
         if(m.mailaccountid <> @prev ,@rn:=1,@rn:=@rn+1) rn,
         @prev:=m.mailaccountid prev
from     (select @rn:=0,@prev:='') p, mails m 
where    foldername = 'inbox'
order    by m.mailaccountid,m.dt desc
) s
where   s.rn <= 3

result

+------+---------------+------------+------------+------+------+
| id   | mailaccountid | foldername | dt         | rn   | prev |
+------+---------------+------------+------------+------+------+
|    3 |             1 | inbox      | 2016-08-03 |    1 |    1 |
|    2 |             1 | inbox      | 2016-08-02 |    2 |    1 |
|    1 |             1 | inbox      | 2016-08-01 |    3 |    1 |
|    6 |             2 | inbox      | 2016-08-03 |    1 |    2 |
|    5 |             2 | inbox      | 2016-08-02 |    2 |    2 |
|    9 |             3 | inbox      | 2016-08-03 |    1 |    3 |
|    7 |             3 | inbox      | 2016-08-01 |    2 |    3 |
|   10 |             4 | inbox      | 2016-08-05 |    1 |    4 |
|   10 |             4 | inbox      | 2016-08-04 |    2 |    4 |
|   10 |             4 | inbox      | 2016-08-03 |    3 |    4 |
+------+---------------+------------+------------+------+------+
Comments