Print top 5 clips of each year based on their ranking

Question

I need to print for each year: the title of the movie, year and rank-in-year of top 5 clips, based on their ranking.

So, i need to find the top 5 rated movies of each year. I've tried a GROUP BY year(releases.date) but i also need the titles and i need to get N-th top rows of each group.

CLIPS [ id, title ]
RELEASES [ id_clip, date ]
RANKS [ id_clip, nr_votes, ranking ]

Any hints ? I am using MySQL. I also have the year in the clips table.

mysql> select * from clip LIMIT 5;
+--------+-----------------------+------+-----------+
| idclip | title                 | year | clip_type |
+--------+-----------------------+------+-----------+
|      4 | !Women Art Revolution | 2010 |         0 |
|      7 | #1                    | 2005 |         0 |
|      8 | #1 (I)                | 2010 |         2 |
|      9 | #1 (II)               | 2010 |         2 |
|     12 | #1 Cheerleader Camp   | 2010 |         2 |
+--------+-----------------------+------

mysql> select * from releasedates LIMIT 5;
+---------+---------------------+---------+
| id_clip | date                | country |
+---------+---------------------+---------+
|       4 | 2010-09-01 00:00:00 | Canada  |
| 1773811 | 2006-01-22 00:00:00 | USA     |
| 1773815 | 2006-02-12 00:00:00 | USA     |
| 1773818 | 2006-02-19 00:00:00 | USA     |
| 1773820 | 2006-01-22 00:00:00 | USA     |
+---------+---------------------+---------+
5 rows in set (0,00 sec)

mysql> select * from ratings LIMIT 5;
+---------+-------+---------+
| id_clip | votes | ranking |
+---------+-------+---------+
|       4 |     8 | 8.0     |
| 1773811 |    51 | 4.5     |
| 1773839 |   753 | 6.3     |
| 1773843 |    32 | 6.9     |
| 1773844 |    18 | 7.1     |
+---------+-------+---------+
5 rows in set (0,00 sec)

If 6 clips are ranked 9.9 and then the next 4 clips are ranked 9.8,9.7,9.6,9.5, do you expect to see 5 rows all 9.9, or do you expect to see 10 rows, 6 of which are 9.9, then a 9.8, 9.7 and 9.6? — Caius Jard, Dec 05 '18 at 10:47
Check https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Caius Jard, Dec 05 '18 at 10:48
Please show the results that you want. The question is ambiguous. What does "top 5" mean? — Gordon Linoff, Dec 05 '18 at 12:26

score 1 · Accepted Answer · answered Dec 05 '18 at 10:29

1

Assuming the id column in Clips is similar to id_clip column in releases, can you try this, I can not test this at the moment, but I think this should work

select Title,Year(date) ,
RANK() OVER(PARTITION BY Year(Date) ORDER BY ranking)
from Clips c
Releases r on c.id=r.id_clip
join Ranks rank on r.id_clip=rank.id_clip

answered Dec 05 '18 at 10:29

Sushant Sharma

86
3

Are you sure that works on the version of MySQL is Cumatru using? Analytics are generally MySQL8/MariaDB10 only – Caius Jard Dec 05 '18 at 10:43
@Cumatru, looks like you have ratings and not rankings in your column, so you can use Order by ranking desc( assuming your MySQL version supports analytics functions) – Sushant Sharma Dec 05 '18 at 10:53

Print top 5 clips of each year based on their ranking

1 Answers1