Getting all occurrences of max per one distinct value in SQL-CodePudding

I am trying to query per decade film with highest rating(or films if there are 2 with highest(max) rating). I am nearly there and my only issue is that if there are 2 films in a decade(with same rating(max rating)), it does not query it. I have tried a lot of different things but nothing seems to work. So far I got

SELECT FLOOR(premiered / 10) * 10 AS Decades,
       title,
       rating
  FROM titles
       INNER JOIN
       ratings ON titles.title_id = ratings.title_id 
 GROUP BY decades

Which returns:

1920    The Kid 8.3
1930    City Lights 8.5
1940    It's a Wonderful Life   8.6
1950    12 Angry Men    9
1960    The Good, the Bad and the Ugly  8.8
1970    The Godfather   9.2
1980    Star Wars: Episode V - The Empire Strikes Back  8.7
1990    The Shawshank Redemption    9.3
2000    The Lord of the Rings: The Return of the King   9
2010    Inception   8.8
2020    Jai Bhim    8.9

My schema looks like:

titles

title_id
title
premiered -> this is the year of movie's release

ratings

title_id
rating

I am not sure how I can get all occurrences of max(rating) per decade(sqlite). My desired result is to get something like this

1920    The Kid 8.3
1920    Another_movie_with_matching_max(rating) 8.3

EDIT: Jarlh suggested to use a subquery to get max rating per decade. I figured it out

SELECT FLOOR(premiered / 10) * 10 AS Decades,
                  rating as rat
             FROM ratings
                  JOIN
                  titles ON ratings.title_id = titles.title_id
            GROUP BY decades
           HAVING max(rating)

Now I am just not sure how to use this subquery to get all films. I tried ->

SELECT FLOOR(premiered / 10) * 10 AS Decades,
       title,
       rating
  FROM titles
       INNER JOIN
       ratings ON titles.title_id = ratings.title_id 
where decades and RATING = (
           SELECT FLOOR(premiered / 10) * 10,
                  rating as rat
             FROM ratings
                  JOIN
                  titles ON ratings.title_id = titles.title_id
            GROUP BY FLOOR(premiered / 10) * 10
           HAVING max(rating) 
           )
           GROUP BY decades

Which does not work as intended

CodePudding user response：

I figured it out. Had to use IN() instead of joining the two tables together.

SELECT FLOOR(premiered / 10)*10 AS str AS decades, rating, title
FROM ratings
JOIN titles ON titles.title_id = ratings.title_id
WHERE (decades, rating) IN
    (SELECT FLOOR(premiered / 10)*10AS decades, MAX(rating)
    FROM ratings
    JOIN titles ON titles.title_id = ratings.title_id
    GROUP BY decades) ORDER BY decades ASC;