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

Question

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

I have tried solution suggested. It works fine If I execute query in MySQL query window but it throw so many errors as Stored Procedure.

Stored Procedure:

CREATE PROCEDURE `SP_GetMailAccountData`()
BEGIN
    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;
END

Error Screenshot:

You have used `limit 5` So naturally you will only get 5 results — Takarii, Aug 05 '16 at 07:54

score 0 · Accepted Answer · answered Aug 05 '16 at 08:02

/*
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 |
+------+---------------+------------+------------+------+------+

Is it solution of `mysql`? I am using `phpmyadmin`. It throw error of `#1054 - Unknown column 'm.mailaccountid' in 'field list' `. Please suggest — Nanji Mange, Aug 05 '16 at 09:03
Solution is mysql and works fine in sql command line and heidisql. I have used table aliases for readability you could try removing the m. prefix — P.Salmon, Aug 05 '16 at 09:26
I want to put your query in Stored Procedure for MySQL but it throw so many errors. Can you please suggest me how can I manage in Stored Procedure? I have also tried by set it as Inline Query. But it throws Fatal error. I think stored procedure would be better option here. — Nanji Mange, Aug 08 '16 at 11:54
Try adding a Delimiter // statement prior to the Create. add // to the end statement and add a new statement at the end of the code Delimiter ; — P.Salmon, Aug 08 '16 at 13:10

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

1 Answers1