Geek Answers Handbook

Get first results per group

I use sql to retrieve the last 20 rows from a table, grouped by date. I would like to limit it so that in each post_day group only the top 10 lines of DESC votes are selected.

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

It looks like this:

 STORYID TIMESTAMP VOTES 1 2015-03-10 1 2 2015-03-10 2 3 2015-03-9 5 4 2015-03-9 3
+5
source share
2 answers

Scheme

 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);

Request

 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;

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)
+1
source

Normally you should use ROW_NUMBER () for each group to order records within each group, and then select the records with ROW_NUMBER <= 10 . There is no aggregated function ROW_NUMBER () in MySQL, but you can use Custom 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 see: How to select the first / smallest / maximum row for each group in SQL

+1
source

More articles:


All Articles