SQL question on correlated sub-queries using windowing rank functions

Question

I have this SQL homework assignment that tells me to list all the customers and the most recent DVD which they have rented, including the title, Genre, Rating, DVD and date of rental. This can be solved via a correlated sub-query or a window rank function

Here is a screenshot of the schema:

Here is what I have tried so far:

Select
    Concat(m.MemberFirstName, ' ', m.MemberLastName) as Member
    , d.DvdTitle
    , g.GenreName
    , rt.RatingName
    , r.RentalRequestDate
From
    Member m 
Inner Join 
    RentalQueue rq on m.MemberId = rq.MemberId
Inner Join 
    DVD d on d.DVDId = rq.DVDId
Inner Join 
    Genre g on g.GenreId = d.GenreId
Inner Join 
    Rating rt on rt.RatingId = d.RatingId
Inner Join 
    Rental r on r.DVDId = d.DVDId

I am not sure how I can use correlated subqueries to answer the question above as I am quite new to correlated subqueries and I would appreciate some help on this. Thanks in advance.

IF you want to be bold; use an Cross apply instead. It's a sub query which allows you to join all the values of your "joins" to a sub query which would simply return the recent rental data for each member. by ordering by rent date desc and limiting the results to the top 1. The cross apply exeuctes this sub query for each member. — xQbert, Jan 24 '22 at 06:22
https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join shows how to use the cross apply when getting n records from a related subquery. which I suppose is correlated because the join to the cross apply is done in the where clause and not on a "on" statement via join. — xQbert, Jan 24 '22 at 06:26
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jan 24 '22 at 09:42
Don't think RentalQueue is relevant at all here. You want to look at actual (prior) rentals regardless of whether they were "in queue". Don't over-complicate. — SMor, Jan 24 '22 at 13:28

score 0 · Answer 1 · answered Jan 24 '22 at 07:40

Try something like this, but please try to understand it too.

Select
    Concat(m.MemberFirstName, ' ', m.MemberLastName) as Member
  , mostrecent.*
From
    Member m 
CROSS APPLY
(select top (1) 
      d.DvdTitle
    , g.GenreName
    , rt.RatingName
    , r.RentalRequestDate
FROM 
    RentalQueue rq 
Inner Join 
    DVD d on d.DVDId = rq.DVDId
Inner Join 
    Genre g on g.GenreId = d.GenreId
Inner Join 
    Rating rt on rt.RatingId = d.RatingId
Inner Join 
    Rental r on r.DVDId = d.DVDId
where m.MemberId = rq.MemberId
order by r.RentalRequestDate desc
) mostrecent

SQL question on correlated sub-queries using windowing rank functions

1 Answers1