1

I have a table with the following scheme (idMovie, genre, title, rating).

How can I make a query that returns the ten films with the best rating for each genre?

I think it could possibly be solved using 'ORDER BY' and also 'LIMIT' to get the top 10 of a genre but I do not know how to do it for each genre.

Disclaimer: I'm newbie in sql.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Donato
  • 73
  • 3
  • 6
  • Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. –  Sep 11 '18 at 19:58

1 Answers1

4

This is a typical problem called greatest-N-per-group. This normally isn't solved using order by + limit (unless you use LATERAL which is more complicated in my opinion), since as you've mentioned it is an answer to problem of greatest-N but not per group. In your case movie genre is the group.

You could use dense_rank window function to generate ranks based on rating for each genre in a subquery and then select those which are top 10:

select title, rating
from (
  select title, rating, dense_rank() over (partition by genre order by rating desc) as rn
  from yourtable
) t
where rn <= 10

This may return more than 10 titles for each genre, because there may be ties (the same rating for different movies belonging to one genre). If you only want top 10 without looking at ties, use row_number instead of dense_rank.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • See also https://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group –  Sep 11 '18 at 20:06
  • Thank you very much for the help, it has become clearer to me what kind of problem I face – Donato Sep 11 '18 at 20:28