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.

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 — , Oct 29 '15 at 00:13

score 1 · Answer 1 · answered Oct 29 '15 at 00:13

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;

answered Oct 29 '15 at 00:13

Gordon Linoff

1,242,037
58
646
786

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? – Oct 29 '15 at 00:14
@user2832891 . . . Probably. This is the way I would do it because window functions are usually faster than correlated subqueries without equality conditions. – Gordon Linoff Oct 29 '15 at 01:38

score 1 · Answer 2 · answered Oct 29 '15 at 00:27

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
     );

answered Oct 29 '15 at 00:27

wildplasser

43,142
8
66
109

Thanks for this - I will test it shortly. Why is the sign flipped for the rating? – Oct 29 '15 at 00:36
@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.) – Erwin Brandstetter Oct 29 '15 at 00:42
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. – Oct 29 '15 at 00:44

SQL nested subqueries

2 Answers2