MYSQL - Get TOP 5 content grouped by value and date

Question

I have a table with columns

id    date   title

I want to get the list of total occurrences of each title per date. So for example get:

date       title       total
20 Apr  - My Title A  - 3
20 Apr  - My Title B  - 2
21 Apr  - My Title A  - 5
22 Apr  - My Title B  - 1
22 Apr  - My Title C  - 1
23 Apr  - My Title A  - 6
23 Apr  - My Title B  - 2
23 Apr  - My Title C  - 5
24 Apr  - My Title C  - 2
24 Apr  - My Title D  - 2

BUT I only want to get the records that have the most values, so from the example above, the total occurrences are:

If I would only want the top 2, then what I would expect is the first list but only filtering top titles:

20 Apr  - My Title A  - 3
21 Apr  - My Title A  - 5
22 Apr  - My Title C  - 1
23 Apr  - My Title A  - 6
23 Apr  - My Title C  - 5
24 Apr  - My Title C  - 2

My first approach is to do a subquery counting the top by title., and then filter where titles are in the list of most counts. But I wanted to know if there is a simpler way.

This kind of group ranking and filtering operation is a bit messy in MySQL: it lacks any `RANK()` operation. Here's the answer to a similar question. https://stackoverflow.com/questions/49898310/how-to-retrieve-number-of-records-from-mysql-database-in-a-group#49899683 — O. Jones, Apr 23 '18 at 18:50
"... MySQL: it lacks any RANK() operation. " Not completely true Oracle has released MySQL 8.0 this month what does support window functions @O.Jones — Raymond Nijland, Apr 23 '18 at 19:17

score 0 · Accepted Answer · answered Apr 23 '18 at 18:57

My initial answer will be the same solution proposed in the question:

Select DATE(date) as date, top.title, count(*) as total  from (
    Select  title, count(*) as total 
    from List
    where date > (current_date - interval 30 day)
    group by title 
    order by total
    desc limit 5) top
 left join List on top.title = List.title 
 where List.date > (current_date - interval 30 day)
 group by DATE(List.date), top.title
 order by date asc

MYSQL - Get TOP 5 content grouped by value and date

1 Answers1