0

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.

Improve this question
3
  • Replace the commas by ANDs . Commented Oct 29, 2015 at 0:11
  • Damnit - been doing Prolog for the last few days and used commas out of habit. Still isn't the query I want however and I'm not sure where I'm going wrong in my thinking Commented Oct 29, 2015 at 0:13
  • Please don't edit the question to correct the errors. Commented Oct 29, 2015 at 0:14

2 Answers 2

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for the suggestion. Bear in mind I am a novice - is there any way to do it in simpler syntax such as how I attempted a nested query?
@user2832891 . . . Probably. This is the way I would do it because window functions are usually faster than correlated subqueries without equality conditions.
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
     );
Improve this answer

3 Comments

Thanks for this - I will test it shortly. Why is the sign flipped for the rating?
@user2832891: Because there shall exist no older album that has greater rating. (Obviously, "year" is not enough for this, you need dates or timestamps - but I guess that's just a simplification in your question.)
Brilliant concise explanation, makes sense. Thank you. How does the query exactly work in this particular example, with the NOT EXISTS subquery? Yes, using an integer for year is just a simplification for now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.