Menu
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
327
Lets say you need to get top 3 records for each category that means to get a top 3 subcategory with the highest amount from each category. To achieve this you can use SQL Server window/Ranking function ROW_NUMBER() Function. Following T-SQL uses SQL server ROW_NUMBER() Function.
The GROUP BY clause can help with that, but it is limited to the single top result for each group. If you want the top 5 per category, GROUP BY won't help by itself. That doesn't mean it can't be done. In fact, in today's blog, we'll learn exactly how to construct a Top N query by group.
No, you can't LIMIT subqueries arbitrarily (you can do it to a limited extent in newer MySQLs, but not for 5 results per group). This is a groupwise-maximum type query, which is not trivial to do in SQL.
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);
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;
+------------+-------+
| 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)
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
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
