Search code examples
mysqlselect-query

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

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: enter image description here

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