Get the top n results per group

Question

I am using the sql to retrieve the last 20 rows from the table grouped by date. I would like to limit it so that within each post_day group only the top 10 rows votes DESC are selected.

SELECT *, DATE(timestamp) as post_day 
FROM stories 
ORDER BY post_day DESC, votes DESC
LIMIT 0, 20

This is what the table looks like:

STORYID         TIMESTAMP           VOTES
1               2015-03-10          1
2               2015-03-10          2
3               2015-03-9           5
4               2015-03-9           3

show some dummy data to demonstrate what you exactly want – Ameya Deshpande Sep 10 '15 at 07:20 — Ameya Deshpande, Sep 10 '15 at 07:20

score 1 · Answer 1 · answered Sep 10 '15 at 07:44

Schema

create table stories
(   storyid int auto_increment primary key,
    theDate date not null,
    votes int not null
);

insert stories(theDate,votes) values 
('2015-03-10',1),
('2015-03-10',2),
('2015-03-09',5),
('2015-03-09',3),
('2015-03-10',51),
('2015-03-10',26),
('2015-03-09',75),
('2015-03-09',2),
('2015-03-10',12),
('2015-03-10',32),
('2015-03-09',51),
('2015-03-09',63),
('2015-03-10',1),
('2015-03-10',11),
('2015-03-09',5),
('2015-03-09',21),
('2015-03-10',1),
('2015-03-10',2),
('2015-03-09',5),
('2015-03-09',3),
('2015-03-10',51),
('2015-03-10',26),
('2015-03-09',75),
('2015-03-09',2),
('2015-03-10',12),
('2015-03-10',44),
('2015-03-09',11),
('2015-03-09',7),
('2015-03-10',19),
('2015-03-10',7),
('2015-03-09',51),
('2015-03-09',79);

The Query

set @rn := 0, @thedate := '';
select theDate, votes
from 
(
   select storyid, theDate, votes,
      @rn := if(@thedate = theDate, @rn + 1, 1) as rownum,
      @thedate := theDate as not_used
  from stories
  order by theDate, votes desc
) A
where A.rownum <= 10;

The Results

+------------+-------+
| theDate    | votes |
+------------+-------+
| 2015-03-09 |    79 |
| 2015-03-09 |    75 |
| 2015-03-09 |    75 |
| 2015-03-09 |    63 |
| 2015-03-09 |    51 |
| 2015-03-09 |    51 |
| 2015-03-09 |    21 |
| 2015-03-09 |    11 |
| 2015-03-09 |     7 |
| 2015-03-09 |     5 |
| 2015-03-10 |    51 |
| 2015-03-10 |    51 |
| 2015-03-10 |    44 |
| 2015-03-10 |    32 |
| 2015-03-10 |    26 |
| 2015-03-10 |    26 |
| 2015-03-10 |    19 |
| 2015-03-10 |    12 |
| 2015-03-10 |    12 |
| 2015-03-10 |    11 |
+------------+-------+
20 rows in set, 1 warning (0.00 sec)

score 1 · Answer 2 · answered Sep 10 '15 at 07:44

Usually you should use ROW_NUMBER() per group to order records inside of each group and then select records with ROW_NUMBER <= 10. In MySQL there is no ROW_NUMBER() aggregate function but you can use User-Defined variables in MySQL to emulate ROW_NUMBER()

select storyId, post_day , votes
from (
   select storyId,
          DATE(timestamp) as post_day, 
          votes,
          @num := if(@grp = DATE(timestamp), @num + 1, 1) as row_number,
          @grp := DATE(timestamp) as dummy
  from stories,(select @num := 0, @grp := null) as T
  order by DATE(timestamp) DESC, votes DESC
) as x where x.row_number <= 10;

SQLFiddle demo

Also look at: How to select the first/least/max row per group in SQL

Get the top n results per group

2 Answers2

Schema

The Query

The Results