SQL nested subqueries

Question

I have a schema "Album" for a music database.

This schema includes attributes: Name, Artist, Rating and Year.

I'm trying to create a query that will allow me to get the names of all albums which have a higher rating than every previous album by the same person. I need to check every tuple in my database and compare tuples where artists are the same, and where the year of one album is greater than the year of another, and also compare rating.

I've tried a few different things all using different strategies. My current attempt is a nested query:

SELECT A1.Title
FROM Album A1
WHERE A1.Title NOT IN (SELECT A2.Title
                FROM Album A2
                WHERE A1.Artist = A2.Artist, A1.Year > A2.Year, A1.Rating > A2.Rating);

This obviously doesn't work (hence my question) - where am I going wrong? I thought a correlated query (like this one) checks every tuple in the table against the subquery? Any clearance on how I could do this query is appreciated. I'm pretty novice at SQL.

Answer 1

(after replacing the commas by ANDs) The NOT EXISTS(...) is similar to NOT IN(...), but behaves nicer if NULLs are involved.

SELECT A1.Title
FROM Album A1
  -- There should not exist an older album with a higher rating
  -- (for the same artist)
WHERE NOT EXISTS (SELECT *
   FROM Album A2
   WHERE A2.Artist = A1.Artist
     AND A2.Year < A1.Year
     AND A2.Rating > A1.Rating -- Note: the sign is flipped, compared to the query in the question
     );

Answer 2

I would use window functions:

select a.*
from (select a.*,
             max(a.rating) over (partition by a.artist
                                 order by a.year
                                 range between unbounded preceding and 1 preceding
                                ) as prev_max_rating
      from album a
     ) a
where rating > prev_max_rating;